MySQL Query Question

[jmrothermel]

Ok - Im trying to make a query using PHP and MySql.  Heres what it says now:

 

$sql = mysql_query("SELECT * FROM `pets` WHERE  `user`='$u' AND `gender`='male' AND `date` < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND `date` > DATE_SUB(CURDATE(), INTERVAL 180 DAY) AND `lastfed` > DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND `lastbreed` < DATE_SUB(CURDATE(), INTERVAL 3 DAY)");

 

Heres what I want to do:

 

I want it to say 'date' > DATE_SUB(CURDATE(), INTERVAL 180 DAY) or 'date' = 00-00-0000  so basically an exception to the within 180 days if the date field has the value 00-00-0000 the result will still be returned.  Ive tried searching google - but didnt come up with anything like this.  Help anyone?

 

Thank you!

[teng84]

sorry  this is not clear enough!

[valtido]

I had this problem aswell lool:)

 

try and use date() as a php function  to get current date;

$date = date('d-m-Y H:i:S',time()- 180); 

note its 180seconds loool jus do the calculation for 180days bla bla bla

 

then on msql it should be something like

 

SELECT * FROM table WHERE date='$date' between 'dd-mm-yy' and 'dd-mm-yy'

 

and look up for "between  AND" operation on mysql to look between two dates

[jmrothermel]

Ok Im sorry that I didnt make myself clear enough - Ive been going at this website since 8 am - 14 straight hours and its probably going to my brain

 

Basically - I want to put an exception into the results it returns.  Right now it returns all values with the Date less than 180 days (which I want) but I also want it to return values where the date value is 00-00-0000 (thats the actual value in the field) so it says now AND `date` > DATE_SUB(CURDATE(), INTERVAL 180 DAY) but I want to add an exception in there so the date has to be either within 180 days or the value 00-00-0000.

 

Did that make sense now?  *sigh* sorry I apologize again 

[valtido]

right ermm this is it then lool:

 

$sql = mysql_querry('SQL GOES HERE WHAT EVER date you wanna select');

 

$date = mysql_fetch_array($sql);//triger it

$date = $date['fieldname'];// printing out the first result of the fieldname

echo date('d-m-Y H:i:S', $date); //  printing out the date in a php format

 

 

 

PS

[teng84]

you can try

 

$sql = mysql_query("SELECT *,if((`date`==00-00-0000 || `date` > DATE_SUB(CURDATE(), INTERVAL 180 DAY)),`date`,'' )  FROM `pets` WHERE  `user`='$u' AND `gender`='male' AND `date` < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND `date` > DATE_SUB(CURDATE(), INTERVAL 180 DAY) AND `lastfed` > DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND `lastbreed` < DATE_SUB(CURDATE(), INTERVAL 3 DAY)");

not tested but it should give you an idea

[Psycho]

Use an OR clause within parens

 

$query = "SELECT *
          FROM `pets`
          WHERE `user`='$u' AND `gender`='male'
            AND (`date` > DATE_SUB(CURDATE(), INTERVAL 180 DAY) OR `date` = '00-00-0000')
            AND `lastfed` > DATE_SUB(CURDATE(), INTERVAL 3 DAY)
            AND `lastbreed` < DATE_SUB(CURDATE(), INTERVAL 3 DAY)";

$sql = mysql_query($query);