Difficulty querying 2 different tables in Database using SQL in PHP document

Question

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

tennisCourts
courtID
courtName
bookingFee
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:

Take the users chosen court booking date and display all fields from the tennisCourts table only if the date the user has chosen is not already taken by another user. I have entered dummy data in the tenniscourts_Availability table.

SELECT and DISPLAY all fields 
from the tennisCourts table (courtID, courtName, bookingFee) 
WHERE court_dateBooked = $CHOSEN_BOOKING_DATE 
   *($_GET is used here to retrieve the booking date enetered by 
     the user in a seperate html document.)

Here is my current code:-

$db_table_tennisCourts = "tennisCourts";
$court_ID = "courtID";
$court_Name = "courtName";
$booking_Fee = "bookingFee";


$db_table_tenniscourts_Availability = "tenniscourts_Availability";
$court_ID = "courtID";
$court_dateBooked = "court_dateBooked";

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


$database->setFetchMode(MDB2_FETCHMODE_ORDERED);


$myQuery = "SELECT $court_ID , $court_dateBooked FROM $db_table_ WHERE $CHOSEN_BOOKING_DATE != $court_dateBooked";

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

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


while ($Col =& $queryResult->fetchRow()) {

echo $queryResult[0].' ';   //shows courtID 

    $queryResult[1];
    list($y,$m,$d)=explode('-',$queryResult[1]);
echo $d.'/'.$m.'/'.$y.'/<br/>';
}

?>

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.

Your question contradicts itself. Also what is the purpose of the tenniscourts_Availability table? Does it store all dates on which a court is available? or all dates on which a court is booked? — liquorvicar, Apr 02 '12 at 16:52
It basically stores all dates on which a court is booked. I just struggled with the structuring of my sql code in order to show only available courtNames NOT those that are already booked. — Daniel Mabinko, Apr 02 '12 at 17:25

Travesty3 · Answer 1 · 2012-04-02T17:02:25.473

Change your query to this:

$myQuery = "
    SELECT DISTINCT
        c.courtID,
        c.courtName,
        c.bookingFee
    FROM
        tennisCourts c
        INNER JOIN tenniscourts_Availability a ON c.courtID = a.courtID
    WHERE
        a.court_dateBooked = '". mysql_real_escape_string($CHOSEN_BOOKING_DATE) ."'
";

This will retrieve the court information for all courts that are booked on the specified date. If you wanted to get the court information for all courts that aren't booked on the specified date, then use this query:

$myQuery = "
    SELECT
        courtID,
        courtName,
        bookingFee
    FROM
        tennisCourts
    WHERE
        courtID NOT IN (SELECT courtID FROM tenniscourts_Availability WHERE court_dateBooked = '". mysql_real_escape_string($CHOSEN_BOOKING_DATE) ."'
";

A few things to note about your code:

The PHP variable you use for your table name ($db_table_) doesn't appear to be defined.
Not sure if you have a special reason for doing this, but from the code you posted, I don't see a need to store the names of the columns in PHP variables. It just confuses things in your example.
Make sure you sanitize any user input. Using input directly from $_GET is vulnerable to SQL injection.

Thanks for your time. Do I have to add the '$' before the c.courtName in order for php to take the value from the variable or should the code above just work as it is? Thanks again for all your help. — Daniel Mabinko, Apr 02 '12 at 17:18
Well, that's my question to you in number 2 in my answer. Is there a reason you need this column name to come from a PHP variable? If not, you should just use it as it is in my answer. If there is a special reason, then you should use `c.$court_Name`, or for clarity, I prefer to use brackets: `c.{$court_Name}`. — Travesty3, Apr 02 '12 at 18:14

score 0 · Answer 2 · edited May 23 '17 at 10:34

Assuming that things are only inserted into the availability table when it's been booked, I think this is what you want.

SELECT c.courtID, c.courtName, c.bookingFee, a.court_dateBooked FROM tennisCourts c 
LEFT JOIN tenniscourt_Avaliability a ON c.courtID=a.courtID
WHERE b.court_dateBooked=$CHOSEN_BOOKING_DATE

you can then filter out which ones have already been booked by checking if court_dateBooked is null or not.

See the following for more on left join: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Difficulty querying 2 different tables in Database using SQL in PHP document

2 Answers2

Linked