dadamssg87 Posted October 27, 2011 Share Posted October 27, 2011 I have this query, but it's not working how i want it. The deleted field holds the timestamp of when the booking was "deleted". Its pulling up bookings that have been deleted and i think it is because i'm misusing the "OR" part in the $sql string. I want to pull bookings between the dates(this part is fine), not deleted, and belong to $rooms that are in the $room_ids array. Anybody know how i can correct this? <?php $sql = ""; foreach($room_ids as $key => $id) { if($key == 0) { $sql .= "room = '$id' "; } else { $sql .= " OR room = '$id'"; } } $query = "SELECT * FROM Bookings WHERE DATE(check_in) AND DATE(last_night) BETWEEN '$first' AND '$last' AND deleted = '0000-00-00 00:00:00' AND $sql"; Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted October 27, 2011 Share Posted October 27, 2011 you don't have an OR in the query you gave us.. what field_type is `deleted`? Quote Link to comment Share on other sites More sharing options...
dadamssg87 Posted October 27, 2011 Author Share Posted October 27, 2011 yes i do. It's in the $sql variable. Deleted is a timestamp Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted October 27, 2011 Share Posted October 27, 2011 i missed $sql sorry, remove the AND in $query and add to $sql if($key == 0) { $sql .= "AND room = '$id' "; } else { $sql .= " OR room = '$id'"; } because right now you would have AND OR room = "$id" which is obviously invalid Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 27, 2011 Share Posted October 27, 2011 This is an AND/OR operator precedence issue. IN() would be a less cumbersome choice to use for this. Assuming $room_ids is populated with integer values: $sql = ""; $rooms = implode( ', ', $room_ids ); $query = "SELECT * FROM Bookings WHERE DATE(check_in) AND DATE(last_night) BETWEEN '$first' AND '$last' AND deleted = '0000-00-00 00:00:00' AND room IN( $rooms )"; Quote Link to comment Share on other sites More sharing options...
mikosiko Posted October 27, 2011 Share Posted October 27, 2011 you can improve your code : - eliminating that foreach completely - replacing in your $query the usage of $sql for: .....AND deleted = '0000-00-00 00:00:00' AND room IN (<here implode your array>) - and here WHERE DATE(check_in) AND DATE(last_night) BETWEEN '$first' AND '$last' what exactly are you trying to do?... I'm guessing that is not what you want... - if you still want to use the foreach and that $sql you will need to enclose that with parentheses ... AND (room = n OR room = m ... etc ) Pika beat me on this... my 3rd point still valid Quote Link to comment Share on other sites More sharing options...
dadamssg87 Posted October 27, 2011 Author Share Posted October 27, 2011 thanks guys. i was wondering if they was a mysql function for that. IN looks like what i'm looking for. As far as the dates go. I'm feeding a function a check_in and last_night dates and room_ids. I need three select queries to pull rows of bookings to do this. 1. One to pull bookings that have both check_in and last_night within the provided dates. 2. One for when the booking starts before the check_in date and ends after the last_night date. 3. One for when the last_night is before the provided last_night date and ends after the provided last_night date I'm using a UNION for the three queries <?php function get_all_bookings_for_user($first,$last,$username) { $year = substr($first, 0, 4); $month = substr($first, 5, 2); $query = $this->db->query("SELECT * FROM Bookings WHERE DATE(check_in) AND DATE(last_night) BETWEEN '$first' AND '$last' AND username = '$username' AND deleted = '0000-00-00 00:00:00' UNION SELECT * FROM Bookings WHERE MONTH(check_in) = '$month' AND YEAR(check_in) = '$year' AND DATE(last_night) > '$last' AND username = '$username' AND deleted = '0000-00-00 00:00:00' UNION SELECT * FROM Bookings WHERE MONTH(last_night) = '$month' AND YEAR(last_night) = '$year' AND DATE(check_in) < '$first' AND username = '$username' AND deleted = '0000-00-00 00:00:00' UNION SELECT * FROM Bookings WHERE DATE(check_in) < '$first' AND DATE(last_night) > '$last' AND username = '$username' AND deleted = '0000-00-00 00:00:00'"); $bookings = array(); $num = 0; foreach ($query->result() as $row) { $num ++; $bookings[$num] = array( 'id' => $row->id, 'name' => stripslashes($row->name), 'check_in' => $row->check_in, 'last_night' => $row->last_night, 'clean_up' => (int) $row->clean_up, 'room_id' => $row->room, 'first_name' => stripslashes($row->first_name), 'last_name' => stripslashes($row->last_name), ); } return $bookings; } ?> Its probably not the most efficient way of doing it but if anyone has suggestions , i'm all ears. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted October 28, 2011 Share Posted October 28, 2011 .... 1. One to pull bookings that have both check_in and last_night within the provided dates. ..... therefore, this is incorrect... you must check both dates individually. WHERE DATE(check_in) AND DATE(last_night) BETWEEN '$first' AND '$last' seems to me that you can do all of that with just one query (no UNION)... use OR to mix group of conditions... try this sql... (no tested): SELECT * FROM Bookings WHERE (DATE(check_in) BETWEEN '$first' AND '$last' AND DATE(last_night) BETWEEN '$first' AND '$last') OR (MONTH(check_in) = '$month' AND YEAR(check_in) = '$year' AND DATE(last_night) > '$last') OR (MONTH(last_night) = '$month' AND YEAR(last_night) = '$year' AND DATE(check_in) < '$first') OR (DATE(check_in) < '$first' AND DATE(last_night) > '$last') AND username = '$username' AND deleted = '0000-00-00 00:00:00' 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.