query help

[dadamssg87]

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";

[AyKay47]

you don't have an OR in the query you gave us..

what field_type is `deleted`?

[dadamssg87]

yes i do. It's in the $sql variable. Deleted is a timestamp

[AyKay47]

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

 

 

[Pikachu2000]

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 )";

[mikosiko]

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

[dadamssg87]

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.

[mikosiko]

....

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'