php - Mysql复杂查询加入

Question

我需要一些帮助来加入下面的查询，因为我相信这是 super 繁重的，While 循环在其他 while 循环中......一团糟，对吧？! :(

这可行，但如果有人可以帮助将其加入到一个查询中:

$query_1 = mysql_query("SELECT * FROM booking_reservation WHERE reservation_confirmed = '1' AND (reservation_name LIKE '$data%' OR reservation_phone LIKE '$data%' OR reservation_email LIKE '$data%')");

    while ($row = mysql_fetch_array($query_1)) {
        $calendar_id = $row['calendar_id'];
        $query_2 = mysql_query("SELECT * FROM booking_calendars WHERE calendar_id = '$calendar_id'");
        $row_2 = mysql_fetch_array($query_2);
        $slot_id = $query_1['slot_id'];
        $query_3 = mysql_query("SELECT * FROM booking_slots WHERE slot_id = '$slot_id'");
        while ($row_3 = mysql_fetch_array($query_3)) {
            echo '<tr><td>' . htmlentities($row2['slot_date']) . '</td><td>' . htmlentities($query_3['slot_time_from']) . '</td><td>' .  htmlentities($query_1['reservation_name']) . '</td><td>' .  htmlentities($query_3['calendar_title']) . '</td><td>' . htmlentities($query_1['reservation_phone']) . '</td><td>' . htmlentities($query_1['reservation_email']) . '</td></tr>';
        }
    }

Answer 1

其中之一应该是您想要的，如果您想获得仅存在于 booking_calendars 和 booking_slots 中的预订，则使用 INNER JOIN，或者如果您想获取仅存在于 booking_calendars 和 booking_slots 中的预订，则使用 LEFT JOIN想要所有预订，无论它们是否存在于其他表中

内连接:

SELECT * FROM booking_reservation br 
INNER JOIN booking_calendars bc ON br.calendar_id=bc.calendar_id 
INNER JOIN booking_slots bs ON br.slot_id=bs.slot_id 
WHERE br.reservation_confirmed = '1' 
AND (br.reservation_name LIKE '$data%' 
OR br.reservation_phone LIKE '$data%' 
OR br.reservation_email LIKE '$data%')

左连接:

SELECT * FROM booking_reservation br 
LEFT JOIN booking_calendars bc ON br.calendar_id=bc.calendar_id 
LEFT JOIN booking_slots bs ON br.slot_id=bs.slot_id 
WHERE br.reservation_confirmed = '1' 
AND (br.reservation_name LIKE '$data%' 
OR br.reservation_phone LIKE '$data%' 
OR br.reservation_email LIKE '$data%')

1个重要提示，不建议使用SELECT *，最好告诉mysql您需要从每个表或您正在选择的表中获取哪些列。

注意:你应该听听 Raptor 所说的，mysql_* 函数已被弃用，并将在以后的 php 版本中删除，你应该开始使用 PDO或mysqli