Need help with LEFT OUTER JOIN query

Here is my database tables and their fields:
[B]jobs:[/B]
id, user_id, company, display_name, cat_id, job_position, status, city, content, submitted

[B]categories:[/B]
cat_id, cat_name

I need something like this:

[code=php]<table border=”0″ cellspacing=”0″ cellpadding=”0″ width=”100%”>

<?php
$query = “SELECT c.cat_id, c.cat_name, COUNT( j.id )
as theCount FROM categories AS c
LEFT OUTER JOIN mont_jobs AS j
ON c.cat_id = j.cat_id
WHERE submitted > SUBDATE(NOW(), INTERVAL 30 DAY)
GROUP BY c.cat_id”;

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$count = $row[‘theCount’];
$cat_name = $row[‘cat_name’];
$cat_id = $row[‘cat_id’];

echo ”
<tr>
<td width=’3%’ valign=’top’ align=’right’><strong>$count</strong> &nbsp;&nbsp; </td>
<td width=’97%’ valign=’top’><a href=’list_jobs.php?file=$cat_id’><!–$cat_id–>$cat_name</a></td>
</tr>
“;
}
?>
</table>
[/code]

[B]What should happen:[/B]

All the job categories from the categories table should be displayed down the right column, and immediately to the left (in the left column) the number of jobs from the jobs table (posted within 30 days of current date) that are listed under that job category name should be displayed.

The connector of the 2 tables being – [B]cat_id[/B], as it is in both tables.

If there are NO jobs listed under a job category name then the number [B]0[/B] should be displayed instead of a blank space.

I’m having trouble wording the query to make this happen.

[B]What is Happening:[/B]

I’m getting this:

1 Healthcare: Medical/ Dental/ Optical
1 Sales/Sales Management
1 Trades/Labor
1 Warehouse/Dockwork

These are the 4 test jobs I entered into the database. The numbers adjacent to the categories are correct. However, I have about 35 job categories in the categories table, and they ALL should be displayed alphabetically. Those with no jobs should have a [B]0[/B] next to them.

I may have to change the cat_id in the jobs table to cat_name. I hate to do this as it would be inefficient listing the category names AGAIN, but at least then I believe I could get it to work. Maybe, I don’t know.

I could use some help in writing the query to get it to do what I want.

It works as it should (almost) when I remove the WHERE clause. But the problem will be that when someone clicks on a job category and is taken to the page that displays all the jobs under that category – it’s only going to show jobs posted within the past 30 days. And the JOIN query will show the number of all the jobs in the database regardless when they were posted.

You can see how this would be misleading. I know that I will have to come up with some kind of cron job or something to remove all jobs from the jobs table that are over 30 days old. But I know absolutely nothing about cron jobs. I can come up with a script to manually delete all jobs over 30 days old, but it’ll be hard to keep up with it every day.

Any way to fix this??

Thanks.

Also in #PHP _↴