Question on quiry phrasing

[lkeeney]

I am trying to select all the rows from my database that match the variables $year and $month. In other words, I am trying to select a months worth of reservations.

My database reservation_date column is stored in this format: year-month-day.

I have come up this this code, which does not work:

$query="SELECT * FROM $table WHERE reservation_date = $year."-".$month."-". AND day BETWEEN 1 AND 31";

I am not surprised that this code doesn't work, but I can't think of another way to phrase it. I have tired other various phrases which did not work either.

Can someone help me on this, and also tell me where I can learn how to phrase the different queries? I have read many examples in books and on the web, but they are all very simple queries.

Lawrence

[r937]

WHERE reservation_date >= DATE(CONCAT('$year','$month','01'))
  AND reservation_date  < DATE_ADD(
                          DATE(CONCAT('$year','$month','01'))
                                  , INTERVAL 1 MONTH)

[lkeeney]

WHERE reservation_date >= DATE(CONCAT('$year','$month','01'))
  AND reservation_date  < DATE_ADD(
                          DATE(CONCAT('$year','$month','01'))
                                  , INTERVAL 1 MONTH)

Thank you for your quick reply.

Can you point me to a source where I can learn to properly construct these queries?

Lawrence

[r937]

as a matter of fact, i can

stay tuned to http://www.sitepoint.com/books/ and watch for an announcement over the next few days for sitepoint's new book about SQL

i think you'll like it

[AnthonySterling]

watch for an announcement over the next few days for sitepoint's new book about SQL

Rudy you Dawg! You can count on me buying it!

With regards to the OP's post, as the data is obtained by PHP it would make more sense to build the date there rather than at the SQL layer.

This would allow the OP to use alternate data for the date without altering the base query.

<?php
$sSQL = sprintf('SELECT * FROM %s WHERE reservation_date >= DATE(%s) AND reservation_date  < DATE_ADD(DATE(%s), INTERVAL 1 MONTH)',
            $sTable,
            $sYear.$sMonth,
            $sYear.$sMonth
        );
?>

[r937]

actually, it would be best if you used php to generate the date of the first day of the month, as well as the date of the first day of the following month

WHERE reservation_date >= '$date1'
  AND reservation_date  < '$date2'

[lkeeney]

as a matter of fact, i can

stay tuned to http://www.sitepoint.com/books/ and watch for an announcement over the next few days for sitepoint's new book about SQL

i think you'll like it

I look forward to reading the book.

[lkeeney]

I want to thank everyone for your suggestions. I am sure one of these will work.

I had not thought of setting up the dates in PHP and submitting a simple, straight forward, query. I should have thought of this myself.

Lawrence