SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question on quiry phrasing

    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:
    Code:
    $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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    WHERE reservation_date >= DATE(CONCAT('$year','$month','01'))
      AND reservation_date  < DATE_ADD(
                              DATE(CONCAT('$year','$month','01'))
                                      , INTERVAL 1 MONTH)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 Code:
    <?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
            
    );
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    WHERE reservation_date >= '$date1'
      AND reservation_date  < '$date2'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •