难以使用PHP文档中的SQL查询数据库中的两个表

Question

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.

Answer 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.