Displaying Database Results Problem

[DavidPr]

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.

include('includes/dbconnect.php');
$query = "SELECT * FROM directory WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY) ORDER BY id DESC";
$result = mysql_query($query);
$num = mysql_num_rows($result);

if ($num == 0)
{
echo "";
}
else
{
echo "
<br><br>

<strong>Recent Business Additions:</strong> (within last 30 days)<br><br>

<center>
<div style='width:75%; text-align:left; border:1px solid #136D86; padding:10px; background-color:#E8E8FF;'>
";

while ($row= mysql_fetch_array($result))
{
$id = $row["id"];
$category_id = $row["category_id"];
$name = stripslashes($row["name"]);
$address = stripslashes($row["address"]);
$city = $row["city"];
$state_id = $row["state_id"];
$zip = $row["zip"];
$phone = $row["phone"];
$url = $row["url"];
$bus_desc = nl2br(stripslashes($row["bus_desc"]));
$show_desc = $row["show_desc"];
$show_web = $row["show_web"];

$query = "SELECT category_name FROM d_category WHERE category_id = $category_id";
$result = mysql_query($query);
while ($row= mysql_fetch_array($result))
{
$category_name = $row["category_name"];


if ($url && $show_web == '1')
{
echo "<a href='http://$url' target='_blank'><strong>$name</strong></a><br>";
}
else
{
echo "<strong>$name</strong><br>";
}





echo "Category: $category_name<br>";






if ($address)
{
echo "$address<br>
$city, $state_id &nbsp;$zip<br>
$phone<br>
";
}
else
{
echo "
$city, $state_id &nbsp;$zip<br>
$phone<br>
";
}
if ($url && $show_web == '1')
{
echo "<a href='http://$url' target='_blank'>$url</a><br><br>";
}
else
{
echo "<br>";
}
$count++ ;
}
}
}
echo "</div></center>";

I'm not sure how to go about fixing this. Any ideas?

Thanks.

[DavidPr]

OK, I tried using a left join and it seems to work.

$query = "SELECT
directory.*, d_category.category_name
FROM directory
LEFT JOIN d_category ON directory.category_id = d_category.category_id
WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY)
ORDER BY id DESC";

[prabirchoudhury]

great ...you could just use Join two tables on the column and declare alias

$query = "SELECT 
d.*, dc.category_name 
FROM directory d
JOIN d_category dc ON dc.category_id = d.category_id 
WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY) 
ORDER BY id DESC";

[DavidPr]

That shortens it up some, thanks.