Adam Posted September 21, 2008 Share Posted September 21, 2008 Hate working with dates in MySQL, but here's the problem: "SELECT * FROM reservations WHERE (datefrom BETWEEN '{$year}/{$month}/01' AND '{$year}/{$month}/{$daysInMonth}')"; That works almost perfectly, except for it won't return reservations that overlap months (in the next month anyway). So if the datefrom date is january 29th, it won't show the reservation in february... The dates are stored in "date" (YYYY-MM-DD) format. I sort of come close to working it out then my mind goes blank and i can't think... doing my head in! Anybody have any possibilities? Cheers, Adam!! Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/ Share on other sites More sharing options...
Adam Posted September 21, 2008 Author Share Posted September 21, 2008 Reckon I've got the logic right, but can't get it to work properly... <?php $resSQL = "SELECT * FROM reservations WHERE (datefrom BETWEEN '{$year}/{$month}/01' AND '{$year}/{$month}/{$daysInMonth}' OR ((datefrom BETWEEN '{$year}/{$month}/01' AND '{$year}/{$month}/{$daysInMonth}') AND (dateto BETWEEN '{$year}/{$nextMonth}/01' AND '{$year}/{$nextMonth}/{$daysInMonth}')))"; ?> What is working is that it's returning any reservations where the starting date is in $month .. however still the overlapping reservations are left out, both any at the start or end of month.. I may have overused brackets starting at the "OR"... All variables are correct, double checked. I think it's just something wrong with either bracket use or slight logic problem. What does a fresh pair of eyes see? Any help appreciated, Cheers! Adam Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/#findComment-647194 Share on other sites More sharing options...
Stefan Posted September 24, 2008 Share Posted September 24, 2008 By the look of your query it seems php provides the values for the month and the year?! I may be wrong but the query looks good to me, it can be the script? Please post more of the script? Maybe this can clarify some things. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html 8 out 10 problems on my scripts are because of MySQL queries so i`m going on a whim here . . Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/#findComment-649461 Share on other sites More sharing options...
Barand Posted September 25, 2008 Share Posted September 25, 2008 If the dates are in YYYY-MM-DD format, why are you comparing against YYYY/MM/DD format? Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/#findComment-650810 Share on other sites More sharing options...
Adam Posted September 26, 2008 Author Share Posted September 26, 2008 Huh yeah not noticed that... Yeah they're stored in YYYY-MM-DD format but for some reason the query still works? Odd! Adam Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/#findComment-650873 Share on other sites More sharing options...
Barand Posted September 26, 2008 Share Posted September 26, 2008 Can you explain what the query is supposed to do? Quote Link to comment https://forums.phpfreaks.com/topic/125137-selecting-dates/#findComment-651444 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.