0


I'm really struggling to get my code to work, and I can't figure out why.

I have a database in my PHPMyAdmin and it contains 2 tables:

  1. tennisCourts
    courtID
     courtName
     bookingFee

  2. tenniscourts_Availability
    courtID
     court_dateBooked

I am writing a PHP program using PEAR repository, and I am struggling to create code that allows me to:

Display All courtNames and their corresponding bookingFee BASED ON users search date ONLY IF the courtName is not already booked by another user.

Here is my current code:-

    $CHOSEN_BOOKING_DATE =  $_GET['user_dateField']; //GET's input data from user form in my other html document.


    $database->setFetchMode(MDB2_FETCHMODE_ASSOC);


    $myQuery = "SELECT * FROM tennisCourts, tenniscourts_Availability WHERE court_dateBooked != $CHOSEN_BOOKING_DATE";


$queryResult =& $db->query($myQuery);

    if (PEAR::isError($queryResult)) {
        die($queryResult->getMessage());
    }

    echo '<table>';
    while ($Col =& $queryResult->fetchRow()) {  
        echo '<td>'.$queryResult['courtName'].'</td>';
        echo '<td>'.$queryResult['bookingFee'].'</td>'; 
        echo '<td>'.$queryResult['court_dateBooked'].'</td>';
    }

?>

The code above displays all the courtNames and BookingFee's for All court_dateBooked fields in my database. I cant get it to display the courtNames and bookingFee only if it isn't booked. If it is booked it should return "sorry no courts found on this date".

I am still new to PHP and SQL so forgive me if I have not made myself clear. I have been researching online and various sources say to use SQL UNION OR JOIN? Could someone please enlighten me on how they could be used in context to my scenario? I really appreciate any help. Thank you for checking out my question.

Daniel Mabinko
  • 1,351
  • 3
  • 11
  • 10
  • You've got an SQL injection hole in your query: http://bobby-tables.com – Marc B Apr 10 '12 at 16:40
  • Is this question different to your previous question? http://stackoverflow.com/questions/9979765/difficulty-querying-2-different-tables-in-database-using-sql-in-php-document – liquorvicar Apr 10 '12 at 17:49

1 Answers1

1

Try this:

$myQuery = "
    SELECT c.courtName
         , c.bookingFee
         , a.court_dateBooked
      FROM tennisCourts AS c
 LEFT JOIN tenniscourts_Availability AS a ON c.id = a.courtID
     WHERE a.court_dateBooked != '" . $CHOSEN_BOOKING_DATE ."'";

Make sure you sanitize and escape $CHOSEN_BOOKING_DATE properly before executing your query. Since it looks like you're using PDO you should be using prepared statements for this.

John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Thankyou very much. How could I now display the message no courts found if court_dateBooked != $CHOSEN_BOOKING_DATE? Thanks again – Daniel Mabinko Apr 10 '12 at 16:51
  • Just check to see how many rows are returned by the query. If it is zero, then you can display your message. – John Conde Apr 10 '12 at 16:55
  • Would I need to place the results of my query into an array to do that? – Daniel Mabinko Apr 10 '12 at 16:59
  • You shouldn't have to. PDO offers a function for getting the number of rows returned from a query. It's called [`rowCount()`](http://php.net/manual/en/pdostatement.rowcount.php) – John Conde Apr 10 '12 at 17:00
  • I just echoed out $queryResult->rowCount(); and it always return 0, even if I input a date that is not already booked. The query you provided above seems to work great, when I input a date that is already booked, it does not populate it in my table however, it populates all of the other dates as they obviously not equal to $CHOSEN_BOOKING_DATE. I cant get it to produce nothing in my table when $CHOSEN_BOOKING_DATE != date_booked. Any ideas. Sorry to keep bothering you John. Thanks – Daniel Mabinko Apr 10 '12 at 17:26
  • Have you tried running this query in MySQL directly? Have you verified that you are getting the results you are expecting? – John Conde Apr 10 '12 at 17:52