[Resolved] Help with mysql date select please.

Dolemite50

Hi,

I have a datetime field and I am trying to find entries in my db from within the last 30 days but somehow nothing is being selected. Can anybody point out what I am doing wrong here?

Thanks

 $thirty_days_ago = date(strtotime('30 days ago'));

$sql5 = "SELECT songid, stitle FROM songs WHERE sdate > '$thirty_days_ago' ORDER BY srating DESC LIMIT 75"; 
$rs = mysql_query($sql5, $conn);
if(mysql_num_rows($rs)) {   

while ($row = mysql_fetch_array ($rs, MYSQL_ASSOC)) {   

$stitle = prepOut($row['stitle']); .......

Thanks for any help. I think it has something to do with the
$thirty_days_ago = date(strtotime('30 days ago'));
and
sdate > '$thirty_days_ago'
part.

Thanks much.

onion2k

$sql5 = "SELECT songid, stitle FROM songs WHERE sdate > date_sub(NOW(), interval 30 days) ORDER BY srating DESC LIMIT 75";

Dolemite50

Whoa!
I've never seen anything similar to that!
Thanks alot, gonna try it now and I will let you know how it went.

Dolemite50

Hmm,
I got this error:

mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/dole50/public_html/listen.php on line 437

I noticed that the query has a single close parantheses
after the

interval 30 days)

Does there need to be a ( somewhere?
Thx again.

Dolemite50

Disregard my last post, I see that it has a ( before NOW().

Anything else that I might be doing wrong here? I am sure the column name is sdate and it is datetime, and there are entries within the last month.

$sql5 = "SELECT songid, stitle FROM songs WHERE sdate > date_sub(NOW(), interval 30 days) ORDER BY srating DESC LIMIT 75";
$rs = mysql_query($sql5, $conn);
if(mysql_num_rows($rs)) {

planetsim

Run the Query in PHPMyAdmin to see if it works?

Dolemite50

It gave me this

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'days) ORDER BY srating DESC LIMIT 75' at line 1

????

Is it not liking the "30"?

Dolemite50

It looks like I found it. It worked when I used "day" instead of "days".

Thanks for getting me in the right direction. 🙂