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! :/ Quote Link to comment Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 Compare how? Quote Link to comment 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... Quote Link to comment Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 And what's wrong with BETWEEN/ Quote Link to comment 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... Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 For what query? That's a mix. Quote Link to comment 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 Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment 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... Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.