Teck Posted November 1, 2007 Share Posted November 1, 2007 heres a small section of my code... $query = "SELECT userid, username, postid, dateline, COUNT(*) AS `postoccurs` FROM forum_post WHERE dateline BETWEEN 1193875200 AND {another dateline} GROUP BY userid ORDER BY userid"; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); while ($row = mysql_fetch_assoc($result)) { $userid = $row['userid']; $username = $row['username']; $postoccurs = $row['postoccurs']; $postid = $row['postid']; $dateline = $row['dateline']; } Anyways, Im trying to use this query & obviously the problem I'm having is, I'm comparing the datelines which isn't doing me much good... Is there a better way to compare the unix timestamps... i dunno, i've been working on this all afternoon & my brain is fried! :/ Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/ Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 Compare how? Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383555 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 Well i want to select only a range of dates... Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383695 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 And what's wrong with BETWEEN/ Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383700 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 And what's wrong with BETWEEN/ It returns me a range of dates im not looking for... Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383709 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 And what's wrong with BETWEEN/ It returns me a range of dates im not looking for... What do you mean? How can it do such a thing? Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383715 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 I have no idea, I've never had a problem using BETWEEN before... IE... I look for results between 2007-05-22 & 2007-09-02... It will return results between the 2 dates but also before 2007-05-22 & after 2007-09-02 Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383719 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 I have no idea, I've never had a problem using BETWEEN before... IE... I look for results between 2007-05-22 & 2007-09-02... It will return results between the 2 dates but also before 2007-05-22 & after 2007-09-02 That seems impossible... are you comparing dates & timestamps? Post some sample data. Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383723 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 That seems impossible... are you comparing dates & timestamps? Post some sample data. Ok so lets say i do between: 2007-10-31 & 2007-11-30 Well, i get data like this: 1192229403 & 2007-10-12 1192316034 & 2007-10-13 1192590308 & 2007-10-16 1192198106 & 2007-10-12 1192051263 & 2007-10-10 1193083001 & 2007-10-22 1192426313 & 2007-10-15 1194017537 & 2007-11-02 1192224043 & 2007-10-12 1192254836 & 2007-10-13 1193507903 & 2007-10-27 1193764122 & 2007-10-30 1192300742 & 2007-10-13 1192822359 & 2007-10-19 1192012799 & 2007-10-10 1193090810 & 2007-10-22 1193876512 & 2007-10-31 1192335514 & 2007-10-13 1192150990 & 2007-10-11 Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383730 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 For what query? That's a mix. Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383737 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 For what query? That's a mix. SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') AS 'date_formatted', userid, username, postid, dateline, COUNT(*) AS `postoccurs` FROM forum_post WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' GROUP BY userid ORDER BY userid Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383740 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 And dateline is a DATE field or a TIMESTAMP field? Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383741 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 And dateline is a DATE field or a TIMESTAMP field? I corrected the query... anyways its a timestamp field Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383743 Share on other sites More sharing options...
Teck Posted November 2, 2007 Author Share Posted November 2, 2007 I was trying to make it all easier to read by removing this part of the query when showing you: WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' AND forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396 When i pull that out (The: " AND forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396", it works... Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383753 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 You're missing parens, it's a precendence thing: WHERE ( 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' ) AND ( forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396 ) but mysql won't get it wrong if you use IN: WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' AND forumid IN (385, 386, 387, 388, 389, 392, 395, 396 ) but you should probably have the parents anyway. Link to comment https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/#findComment-383768 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.