MySQL Query Advice ...

[bsamson]

Hello, I am not sure the best way to approach this, but here's the situation ...

 

I have a table structured like so:

STORE  |  DAY15  |  DAY45  |  DAY90

13    | 05/01/08 | 06/09/08 | 05/23/08

28    | 05/02/08 | 06/01/08 | 05/15/08

13    | 05/04/08 | 06/02/08 | 06/09/08

 

Now, obviously the actual table contains more data.

 

What I am trying to do is create a mysql query that counts the number of of dates (ie. DAY15, DAY45, & DAY90 date fields) for all STORE numbers.

 

In other words given the data above, I want these results:

STORE 13 has 2 matching

STORE 28 has 0 matching

 

I hope I explained this well enough, but if not please let me know. Thanks in advance for any assistance!

[craygo]

This is not tested but try this out.

<?php
$x=0;
$today = time();
$sql = "SELECT `STORE`, UNIX_TIMESTAMP(`DAY15`) AS day15, UNIX_TIMESTAMP(`DAY30`) AS day30, UNIX_TIMESTAMP(`DAY45`) AS day45 FROM table";
$result = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($result)){
$r['day15'] < $today ? $x++ : "";
$r['day30'] < $today ? $x++ : "";
$r['day45'] < $today ? $x++ : "";
echo "Store {$r['STORE']} has $x matching";
$x = 0;
}
?>

 

Ray