Finetuning mysql query!

[Perttu Pulkkinen]

I already got some good help earlier, but still need some..:-)

I have tables like
ADVERTISERS:
advertiser_id
advertiser_name etc...

ADS:
ad_id
ad_advertiser_id
ad_status (0 or 1 where 1 means visible)
ad_duration (usually 6 months expressed in seconds = 15811200)
ad_start (php function time() set during first publishment)
ad_text etc...

In same query i want to get
- all information about advertisers and
- number of visible, no-outdated ads that they have.

It would be a lot easier if everybody had at least one ad and active state
of ads wouldn't matter. But that's not the case and now I have to do JOIN
and this and that.

************************************************** **
This ALMOST works but counting only active, no-outdated ads doesn't work:

$now=time();
$query =" SELECT
IF ((ads.ad_id <> NULL AND ads.ad_status=1 AND (ad_duration + ad_start) >
'$now'), count( * ), 0) AS nr_of ads,
advertisers.*
FROM advertisers
LEFT OUTER JOIN ads ON ad_id=ad_advertised_id
GROUP BY advertiser_id
ORDER BY advertiser_name
");

[Perttu Pulkkinen]

Actually current test is, but doesnt' work right:

$now=time();
$query =" SELECT
IF ((ads.ad_id <> NULL AND ads.ad_status=1 AND (ad_duration + ad_start) >
'$now'), count( * ), 0) AS nr_of ads,
advertisers.*
FROM advertisers
LEFT OUTER JOIN ads ON advertiser_id=ad_advertised_id
GROUP BY advertiser_id
ORDER BY advertiser_name
");

[Perttu Pulkkinen]

Ok, I found the solution already.

[Aggro]

Perttu Pulkkinen wrote:

$now=time();
$query =" SELECT
IF ((ads.ad_id <> NULL AND ads.ad_status=1 AND (ad_duration + ad_start) >
'$now'), count( * ), 0) AS nr_of ads,

Not relevant to your problem but...

You do know that MySQL has now() function, which return current date and
time, and also curdate() and curtime() which return only the current
date or time.

So instead using $now() = time(); You could just say
"select * from tablename where timecolumn=curtime()";