WHERE clause

[DavidPr]

I'm running a small classified ads site and I display a list of categories with the number of ads listed under it.

The ads stay in the database for 40 days and then they are deleted using a cron script. However, they're only displayed for 30 days. The extra 10 days are to give the owner time to renew the ad whereby it'll be displayed for another 30 days. This part I have no trouble with because I have this in the query:

FROM ads WHERE cat_name='$cat_name' &&
submitted > SUBDATE(NOW(), INTERVAL 30 DAY)

The problem I'm having is with the part I mention first, displaying the number of ads next to the category name that the ad is filed under. I'm using this:

$query = "SELECT c.cat_id, c.cat_name, COUNT(d.cat_name)
as theCount FROM class_categories AS c LEFT OUTER JOIN ads AS
d ON c.cat_name = d.cat_name GROUP BY c.cat_name";

However, this number includes the ads that aren't currently displayed since they're over 30 days old. I've tried using this:

$query = "SELECT c.cat_id, c.cat_name, COUNT(d.cat_name)
as theCount FROM class_categories AS c LEFT OUTER JOIN ads AS
d ON c.cat_name = d.cat_name WHERE cat_name='$cat_name' &&
submitted > SUBDATE(NOW(), INTERVAL 30 DAY)
GROUP BY c.cat_name";

But, it doesn't work. It only displays the first category name that has an ad under it.

Any idea how I can get it to display the number of ads (that are within the 30 day display time limit) that each category has?

Thanks
David

[Atli]

Hi.

You can specify more than one condition for joins, just as you would with a WITH clause.

So, you could do:

SELECT *
FROM a
INNER JOIN b
  ON a.id = b.a_id
  AND b.a_id Mod 2 = 0
 

And the table would only display rows where the two tables are linked together AND where the id in table a is an even number.

You should be able to add your date limitation to your join in the same way.

[DavidPr]

Atli,

Thanks, works great!

$query = "SELECT c.cat_id, c.cat_name, COUNT(d.cat_name)
as theCount FROM class_categories AS c LEFT OUTER JOIN ads AS
d ON c.cat_name = d.cat_name
AND submitted > SUBDATE(NOW(), INTERVAL 30 DAY)
GROUP BY c.cat_name";