no rows returned in php - works as a direct query though

probably syntax, can someone take a look?

[neophyte]

Hi All -

I can run the following on a direct query that returns row numbers, but can't get it working via php - rows returned are 0, which isn't true.

Here's what I've got:

$checkIn = $arriveYear . "-" . $arriveMonth . "-" . $arriveDay;

$checkOut = $departYear . "-" . $departMonth . "-" . $departDay;

$query="SELECT * FROM occupancy WHERE CheckIn <= $checkIn AND CheckOut >= $checkOut";

$result = dbQuery($query, TRUE);

$stdBooked = $result['rows'];

echo ("std rooms booked: " . $stdBooked);

************

So, nothing is being returned. Hummm. The row count should be atleast "10" - depending on what's inside the $checkIn and $checkOut variables.

But, when I do:

select * from `pelican`.`occupancy` WHERE CheckIn >= '2006-01-20' AND CheckOut <= '2006-04-24'

directly into the database, no problem.

Could someone help me find the error of my ways?

All help tremendously appreciated.

Neophyte

[henry0]

$query="SELECT * FROM occupancy WHERE CheckIn <= $checkIn AND CheckOut >= $checkOut";

Should'nt it be the other way around

$query="SELECT * FROM occupancy WHERE CheckIn <= $checkOut AND CheckOut =>$checkIn ";
also why should it be = unless rooms are booked by the hour :)

Hope I understood your concept.

[eelixduppy]

Try changing your query to this:

$query="SELECT * FROM occupancy WHERE CheckIn <= '".$checkIn."' AND CheckOut >= '".$checkOut."'";

Make sure that your dbQuery function is returning the correct stuff. It seems a little strange how you are retrieving the data. Also, try adding some debugging features to this function to see if mysql is throwing any errors:

mysql_query($query) or [url=http://us3.php.net/manual/en/function.die.php]die[/url]([url=http://us3.php.net/mysql_error]mysql_error[/url]());

You can also echo the query to the browser and then manually check that in the database to see if it returns anything.

Good luck

[edit]oops...too long to post ;)[/edit]

[Romeo]

... and after the '$query="SELECT...' put in a
echo "the query string looks like this: $query ";
to be sure about what you really send to the database, as there may be previous errors crept in during the generation of previous data like the $arrive... variables, etc.

Kind regards,
R.

[neophyte]

Henry O, eelixduppy, Romeo -

HUGE thanks go out to the three of you for the following reasons:

Henry O:

I have been fighting and fighting for a way to isolate all records that fall both within and without a given date range. I finally thought I'd have to do it with a double query for each room type (due to the "=") but your simple solution eliminated that issue! Thanks for spotting it!

PS: Here in the Philippines it is true that a lot of hotels do reserve/charge rooms by the hour - 3 guesses why and the first 2 don't count - but this is not the case for my particular client, however.

eelixduppy:

Your solution for bracketing the $checkIn and $checkOut variables with ."' and "'. made my query work. I've also tried it with only single quotes surrounding my variables and that works as well.

A follow-on to this: is the implementation of a ."' and '". paring (rather than just ' ') a "best practices" issue, or are there DB scenarios where ."' and "'. will keep me out of trouble?

Romeo and eelixduppy -

Echoing the query string to the browser - as you both recommended - did give me the first "ah, ha" that things were amiss - so obvious, why didn't I think of that?

Thanks again to you all for your help!

Neophyte