473,324 Members | 2,456 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Displaying Database Results Problem

155 100+
I have a business directory and on the main page I want to show the businesses that have signed up in the past 30 days. This hasn't been a problem until I decided to try and show the category the business was listed under in the list of new businesses.

The way the databases are setup currently is I have a table with the directory categories:

category_id
category_name

In the table that holds the business information I have a row: category_id

which holds the numerical id number of the category instead of the name.

I tried the code below but the result was that it only displayed the last business that joined instead of all in the last 30 days.

Expand|Select|Wrap|Line Numbers
  1. include('includes/dbconnect.php');
  2. $query = "SELECT * FROM directory WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY) ORDER BY id DESC";
  3. $result = mysql_query($query);
  4. $num = mysql_num_rows($result);
  5.  
  6. if ($num == 0)
  7. {
  8. echo "";
  9. }
  10. else
  11. {
  12. echo "
  13. <br><br>
  14.  
  15. <strong>Recent Business Additions:</strong> (within last 30 days)<br><br>
  16.  
  17. <center>
  18. <div style='width:75%; text-align:left; border:1px solid #136D86; padding:10px; background-color:#E8E8FF;'>
  19. ";
  20.  
  21. while ($row= mysql_fetch_array($result))
  22. {
  23. $id = $row["id"];
  24. $category_id = $row["category_id"];
  25. $name = stripslashes($row["name"]);
  26. $address = stripslashes($row["address"]);
  27. $city = $row["city"];
  28. $state_id = $row["state_id"];
  29. $zip = $row["zip"];
  30. $phone = $row["phone"];
  31. $url = $row["url"];
  32. $bus_desc = nl2br(stripslashes($row["bus_desc"]));
  33. $show_desc = $row["show_desc"];
  34. $show_web = $row["show_web"];
  35.  
  36. $query = "SELECT category_name FROM d_category WHERE category_id = $category_id";
  37. $result = mysql_query($query);
  38. while ($row= mysql_fetch_array($result))
  39. {
  40. $category_name = $row["category_name"];
  41.  
  42.  
  43. if ($url && $show_web == '1')
  44. {
  45. echo "<a href='http://$url' target='_blank'><strong>$name</strong></a><br>";
  46. }
  47. else
  48. {
  49. echo "<strong>$name</strong><br>";
  50. }
  51.  
  52.  
  53.  
  54.  
  55.  
  56. echo "Category: $category_name<br>";
  57.  
  58.  
  59.  
  60.  
  61.  
  62.  
  63. if ($address)
  64. {
  65. echo "$address<br>
  66. $city, $state_id &nbsp;$zip<br>
  67. $phone<br>
  68. ";
  69. }
  70. else
  71. {
  72. echo "
  73. $city, $state_id &nbsp;$zip<br>
  74. $phone<br>
  75. ";
  76. }
  77. if ($url && $show_web == '1')
  78. {
  79. echo "<a href='http://$url' target='_blank'>$url</a><br><br>";
  80. }
  81. else
  82. {
  83. echo "<br>";
  84. }
  85. $count++ ;
  86. }
  87. }
  88. }
  89. echo "</div></center>";
I'm not sure how to go about fixing this. Any ideas?

Thanks.
May 28 '09 #1
3 1172
DavidPr
155 100+
OK, I tried using a left join and it seems to work.

Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT
  2. directory.*, d_category.category_name
  3. FROM directory
  4. LEFT JOIN d_category ON directory.category_id = d_category.category_id
  5. WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY)
  6. ORDER BY id DESC";
May 28 '09 #2
prabirchoudhury
162 100+
great ...you could just use Join two tables on the column and declare alias

Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT 
  2. d.*, dc.category_name 
  3. FROM directory d
  4. JOIN d_category dc ON dc.category_id = d.category_id 
  5. WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY) 
  6. ORDER BY id DESC"; 
  7.  
  8.  
  9.  
May 29 '09 #3
DavidPr
155 100+
That shortens it up some, thanks.
May 29 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: David | last post by:
I am retreiving information from a database and it works ok, but its rather a lot and I like to limit each web page to 10 rows of data, then put the next ten on a new web page, probably with a...
6
by: Francisco | last post by:
I have this question: I have a simple search to a db, something like: "select description from games where year = '1990'" suppose I get 300 results, I would like to display this in pages of 30...
2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
14
by: Rahul Chatterjee | last post by:
Hello All I have an asp page in which I am performing the following 1. Querying a database view 2. Returning rows in to a recordset. 3. Looping thru the recordset and printing the data 4....
7
by: smd | last post by:
Hello and thanks for taking the time to read this. I've looked all over the web and newsgroups and can't find a solution to my problem. I've posted this question to the Access 2000 group as well -...
5
by: kbrad | last post by:
I have an Access database set up with a number of text fields and a hyperlink field which references a different image per record. I am trying write ASP code to retrieve allt his data and images...
1
by: Brave | last post by:
I have MS Access databases that are viewed via ASP pages made in Frontpage. I have two issues that have the same problem. 1: When someone views database results on one of my webpages, and...
13
by: David W. Fenton | last post by:
I've been struggling the last two days with something I thought was very easy, which is to open a web page with a form on it and populate the form with data passed in a query string (either POST or...
2
by: chazzy69 | last post by:
I having trouble displaying information i have retrieved from an mysql database, i succesfully execute the SELECT query as shown below- $sql = "SELECT PCODE,LOCALITY,STATE FROM `postcode` WHERE...
3
by: Nightcrawler | last post by:
I have a website that does the following: 1. it accepts a keyword through a textbox in the UI 2. once the submit button is clicked it goes out and spiders a few websites using the keyword...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.