limitphp Posted January 8, 2009 Share Posted January 8, 2009 I have a query: <?php $sqlRowCount = "SELECT * FROM vote, songs WHERE vote.songID=songs.songID AND songs.genre IN ($genres) AND vote.date > NOW()-INTERVAL $time DAY"; $resultRowCount = mysql_query($sqlRowCount) or die (mysql_error()); $numrows = mysql_num_rows($resultRowCount); If I put in 1 for $time and in my vote table I only have a maximum of 4 entries that have been voted on in the past 24 hours (date is equal to or greater than Jan. 7 or later), shouldn't I only get a maxiumum of 4 results from this query? Right now its returning 6 results. Does anyone know how this can be? I really don't have any idea? Did I do the "vote.date > NOW()-INTERVAL $time DAY" correctly? Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/ Share on other sites More sharing options...
premiso Posted January 8, 2009 Share Posted January 8, 2009 What is $time being defined as? Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732519 Share on other sites More sharing options...
limitphp Posted January 8, 2009 Author Share Posted January 8, 2009 I have this to validate it: <?php if(empty($time) || !is_numeric($time) || $time>365){ $time = 30; } but I don't think I actually set it as any type....are you supposed to set the variables type? I don't think I know how to do that in php... Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732525 Share on other sites More sharing options...
limitphp Posted January 8, 2009 Author Share Posted January 8, 2009 What is $time being defined as? I'm sorry...you meant what value....I'm setting it as 1. Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732535 Share on other sites More sharing options...
limitphp Posted January 8, 2009 Author Share Posted January 8, 2009 I should also note that the date in the vote table is set as type datetime. Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732540 Share on other sites More sharing options...
premiso Posted January 8, 2009 Share Posted January 8, 2009 Try this: vote.date > DATE_SUB(NOW(), INTERVAL $time DAY) And see what happens. Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732542 Share on other sites More sharing options...
Brian W Posted January 8, 2009 Share Posted January 8, 2009 How many rows are in the database? Is there anything in common with the 6 returned? Are they random or is it pulling an extra day? Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732545 Share on other sites More sharing options...
limitphp Posted January 8, 2009 Author Share Posted January 8, 2009 ok this is the weirdest thing.....and you might not believe me, but it really happened.... the records that were within 24 hours actually ran out....they were at jan 6....at like 9 something am.... its 10 something here now.... anyway...I wasn't getting any hits for 24 hours...and I wondered why....so I found that out.... anyway...I changed the time on them to 11 am....and now it works good for time = 1. I have 4 records that were within the last 24 hours, and now I only get 4 records.... but I tried putting in time = 2...and in the database, there are still only those 4 records that are within 2 days.....but its giving me 7 results..... so now, 1 day works but 2 days doesn't work! I tried putting in your DATE_SUB(NOW(), INTERVAL $time DAY) I get the exact same results as with the NOW()-INTERVAL $time DAY man....I am really confused here.... Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732560 Share on other sites More sharing options...
premiso Posted January 8, 2009 Share Posted January 8, 2009 2 days should return more results...no? Why not print out the TIME in your while loop after returning the data and verify that they are not within the 2-day mark. I bet they are. Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732561 Share on other sites More sharing options...
limitphp Posted January 8, 2009 Author Share Posted January 8, 2009 2 days should return more results...no? Why not print out the TIME in your while loop after returning the data and verify that they are not within the 2-day mark. I bet they are. ok I'm an idiot...... I was looking at the phpadmin view at the table...and I didn't realize there was a page 2 with 3 more results! it was working......I can't believe you knew that was probably the problem! thanks guys.... Link to comment https://forums.phpfreaks.com/topic/140009-solved-help-with-time-interval-in-query/#findComment-732566 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.