2

I'm trying to find available rooms based on booking dates which are stored in my DB with the roomId, start date and end date. The user will search for a start and end date and I query that table to see if there's any existing bookings within those dates. Trouble is, the results I'm getting back in PDO are inconsistent with those in phpmyadmin, or when I hard code the dates straight into the query and I can't figure out why. My database and all its tables and columns are in UTF-8, as is my PDO connection. I have split out the code where I check the bookings table so it is isolated but I'm still having the problem. A real example is:

Existing bookings:

 ----------------------------------------------
| BookingId | roomId | start_date |  end_date  |
 ----------------------------------------------
|     1     |    1   | 2015-07-02 | 2015-07-15 |
 ----------------------------------------------
|     2     |    1   | 2015-07-20 | 2015-07-30 |
 ----------------------------------------------

user queries for availability between 16th July and 18th July and for some reason it returns BookingId 1 which it shouldn't! The logic in my query appears correct to me, and returns the correct number of results (ie none) when I run the same query in phpmyadmin. Here's the code:

<?php
  require_once("connection.php");
  require_once("ex_dmp.php");
  $con = connect_db();

  $arrDate = urldecode($_GET['arr']);
  $deptDate = urldecode($_GET['dept']);

  $arr =  date('Y-m-d',strtotime($arrDate));
  $dept = date('Y-m-d',strtotime($deptDate));

  $queryStr = "SELECT `id`, `start_date`, `end_date` FROM `bookings` b ".
               "WHERE (b.`start_date` < :dept and b.`end_date` >= :dept) ".
               "OR (b.`start_date` <= :arr and b.`end_date` > :arr) ".
               "OR (b.`start_date` >= :arr and b.`end_date` <= :dept)";

  $query = $con->prepare($queryStr);

  $query->bindParam(':arr', $arr, PDO::PARAM_STR);
  $query->bindParam(':dept', $dept, PDO::PARAM_STR);

  $query->execute();
  $bookings = $query->fetchAll(PDO::FETCH_ASSOC);
  var_dump($bookings);
?>

However if I replace the query with hard coded dates and delete the bindParam lines, I get the correct results (none)

  $queryStr = "SELECT `id`, `start_date`, `end_date` FROM `bookings` b ".
               "WHERE (b.`start_date` < '2015-07-18' and b.`end_date` >= '2015-07-18') ".
               "OR (b.`start_date` <= '2015-07-16' and b.`end_date` > '2015-07-16') ".
               "OR (b.`start_date` >= '2015-07-16' and b.`end_date` <= '2015-07-18')";

Can anyone see a problem with it, or know of any other reason why I might be having this problem?

Thanks for your help

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
ministe
  • 543
  • 1
  • 5
  • 17
  • 5
    http://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders You can **NOT** re-use placeholder names unless you're using emulated prepares. – Marc B May 06 '15 at 15:13
  • can you please post what `var_dump($_GET);` outputs? – Jonathan May 06 '15 at 15:15
  • But of course ^ - Marc's comment. – Funk Forty Niner May 06 '15 at 15:15
  • Add some [error checking](http://php.net/manual/en/mysqli.error.php) to your queries and connections. – Jay Blanchard May 06 '15 at 15:15
  • ah, I think I've had that issue before actually @MarcB. So how best to do this, just call my parameters arr1, arr2 etc? – ministe May 06 '15 at 15:19
  • 1
    yeah. basically each one has to be unique. how you make them unique is up to you. pain the tuckus... would be nice if you could re-use them, since (just like in your query) single values do need to get re-used in all kinds of situations. Alternatively you could use the reverse `WHERE :dateval BETWEEN startfield AND endfield`. then you'd only need 3 placeholders. – Marc B May 06 '15 at 15:20
  • could you show your `require_once("connection.php");` please? – Alex May 06 '15 at 15:32
  • great, thanks @MarcB that's sorted it! If you want to re-post your comment as an answer, I'll mark it correct – ministe May 06 '15 at 18:49

0 Answers0