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

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

我的PHPMyAdmin中有一个数据库,它包含2个表:

tennisCourts
courtID
courtName
bookingFee

tennisCourts courtID courtName bookingFee
tenniscourts_Availability
courtID
court_dateBooked

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:

我正在使用PEAR存储库编写PHP程序,我正在努力创建允许我的代码:

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.

只有当用户选择的日期尚未被其他用户使用时,才能选择用户选择的法院预约日期并显示来自tennisCourts表的所有字段。我在tenniscourts_Availability表中输入了虚拟数据。

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.

我仍然是PHP和SQL的新手,所以请原谅我,如果我还没弄清楚的话。我一直在网上研究,各种消息来源说使用SQL UNION OR JOIN?有人可以请教我如何在我的场景中使用它们吗?我非常感谢任何帮助。感谢您查看我的问题。

2 个解决方案

#1

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.

您用于表名($ db_table_)的PHP变量似乎未定义。

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.

不确定您是否有特殊原因这样做,但是从您发布的代码中,我认为不需要在PHP变量中存储列的名称。它只是混淆了你的例子中的东西。

Make sure you sanitize any user input. Using input directly from $_GET is vulnerable to SQL injection.

确保清理任何用户输入。直接使用来自$ _GET的输入很容易受到SQL注入的攻击。

#2

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.

然后,您可以通过检查court_dateBooked是否为空来过滤掉已经预订的那些。

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

有关左连接的更多信息,请参阅以下内容:“INNER JOIN”和“OUTER JOIN”之间的区别是什么?

#1