Jump to content

query help


dadamssg87

Recommended Posts

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

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

....

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'  

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.