date interval not working

@kprocMar 21.2007

this does work the datefiff is not doing what is should, no values are being returned regardless of the value set for > 20

[code=php] $query_get_newest_ad = (“SELECT ad_date, title, ad_id, member_id, description FROM ads WHERE (DATEDIFF(CURDATE(), ad_date) > 20) OR status != ‘Delete’ ORDER by ad_date DESC Limit 2”); [/code]

this does work ?

[code=php] $query_get_expired_ads = (“SELECT * FROM ads WHERE DATEDIFF(CURDATE(), ad_date) > ’20′”);

[/code]

to post a comment

PHP

11 Comments(s) _↴

@NightShift58Mar 21.2007 — #CURDATE() returns a string and so does ad_date, if it's a datetime field. Your observation is correct.

@kprocauthorMar 21.2007 — #I guess my question was not very clear. I want the query that doe snot work to some how work. If I remove "OR status != 'Delete' ORDER by ad_date DESC Limit 2" the query works but this as part of the query somehow

@TaschenMar 21.2007 — #In your first example wrap the 20 in quotes (as you have done in the second example).

@kprocauthorMar 22.2007 — #the second example does not work.

@kprocauthorMar 22.2007 — #Here is the code in two queries, I want to accomplish the same thing in one query. (Note: this does not really work as it shows the results twice)

[code=php]
  $query_get_expiring_ads = ("SELECT ad_id FROM ads WHERE DATEDIFF(CURDATE(), ad_date) > 20");
 
 $get_expired_ads = mysql_query($query_get_expiring_ads)or die (mysql_error());
 
 while( $row_expired_ads = mysql_fetch_assoc($get_expired_ads)){
 $ad_id =  $row_expired_ads['ad_id'];
 
 $query_get_all_expiring_ads = ("SELECT * FROM ads WHERE ad_id = '$ad_id' OR status != 'Delete'");
 
 $get_all_expired_ads = mysql_query($query_get_all_expiring_ads)or die (mysql_error());
 while( $row_all_expired_ads = mysql_fetch_assoc($get_all_expired_ads)){
 
 $ad_title = $row_all_expired_ads['title'];
 
 echo $ad_title.'<br>';
 }
 }
 [/code]

@NightShift58Mar 22.2007 — #Does this make a difference?

[code=php]<?php
 $query_get_expiring_ads = "
 SELECT ad_id FROM ads 
 WHERE DATEDIFF(CURDATE(), ad_date) > 20
 OR status <> 'Delete'
 ";
 ?>[/code]

Also, it seems to me - just based on the wording of the query - that perhaps instead of [B]OR[/B] you may really want [B]AND[/B], meaning:

A list of all ads that are older than 20 days

[B]AND[/B]

which have not yet been deleted

@kprocauthorMar 24.2007 — #This is still creating me some greif.

this does not work

any ideas why this query will not work.

[code=php]
 $query_get_newest_ad = ("SELECT * FROM ads WHERE DATEDIFF(CURDATE(), ad_date) > 28 AND status != 'Delete' AND status !='Sold' AND status!='Expired' ORDER by ad_date DESC Limit 2");
 
 [/code]

This Does work

[code=php]
  $query_get_expiring_ads = ("SELECT * FROM ads WHERE DATEDIFF(CURDATE(), ad_date) > 30 AND status != 'Delete' AND status !='Sold' AND status !='Expired'");
 [/code]

@NightShift58Mar 24.2007 — #How does it not work? Error message or no and/or wrong data?

@kprocauthorMar 24.2007 — #The query does not limit the entry's based on the != status that are not work for the code that does not work. no error messages are given

@NightShift58Mar 24.2007 — #

[code=php]
 $query_get_newest_ad = "
 SELECT * FROM ads 
 WHERE (DATEDIFF(CURDATE(), ad_date) > 28) 
 AND status <> 'Delete' 
 AND status <> 'Sold' 
 AND status <> 'Expired' 
 ORDER BY ad_date DESC 
 LIMIT 2
 ");
 [/code]

The word "status" is not officially a reserved but, sometimes, MySQL reacts allergically to such words being used without the backticks or the table alias.

Otherwise, I can't see why one query would work and not the other, other than that by using ORDER BY, MySQL starts working with aliases and may get thrown off by the "status" keyword.

Try the same query as written above...

@NightShift58Mar 24.2007 — #Got the code. Quick reply...

You need a space between [B]FROM adsWHERE[/B]

Also in #PHP _↴

Which PHP Function Determines Link & File Types ?How to check if a object is set Which PHP CMS to Serve Clojurescript Frontend

Success!

Help @kproc spread the word by sharing this article on Twitter...

Tweet This

date interval not working

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

date interval not working

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴