where语句中的MySQL where语句

Question

我似乎对现有MySQL循环中的WHERE语句有问题，这使我认为这是不可能的。如果它是可行的，我将如何执行？

这个例子使用它现有的连接，我也尝试过它自己的连接，但没有成功。

我有两张桌子，预订和工作。预订是数据库的主要描述部分，作业可能有几行与预订相同的预订有关。

<?php
    $conn = mysqli_connect($servername,$username,$password,$dbname);

    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT * FROM rwxoq_Bookings WHERE user_id = $user_id";
    $result = mysqli_query($conn, $sql);
    while($rowb = mysqli_fetch_assoc($result)) { 
?>
    <tr>
      <td valign="top">
        <?php echo $rowb{'Booking_ID'};?>
      </td>
      <td>
       <?php echo date("D, d-m-Y g:ia", strtotime($rowb{'created'}));?>
      </td>
      <td>
        <?php
         $sqlj = "SELECT * FROM rwxoq_jobs WHERE Booking_ID = $rowb{'Booking_ID'}";
         $resultj = mysqli_query($conn, $sqlj);
         while($rowj = mysqli_fetch_assoc($resultj)) {
             $count++;
         }
        echo $count;
?>
      </td>
      <td id="<?php echo $row{'id'};?>">PENDING</td>
    </tr>
<?php } ?>

Answer 1

我知道你会怎么做，但这不是一个好的解决办法；

我建议你在桌子之间用一个连接：

$conn = mysqli_connect($servername,$username,$password,$dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT b.*, COUNT(*) AS cnt FROM rwxoq_Bookings b LEFT JOIN rwxoq_jobs j ON j.Booking_ID = b.Booking_ID WHERE user_id = $user_id GROUP BY b.Booking_ID";
$result = mysqli_query($conn, $sql);
$table = "";
while($rowb = mysqli_fetch_assoc($result)) { 

    $table .= "
    <tr>
      <td valign='top'>
        {$rowb['Booking_ID']}
      </td>
      <td>
       ". date('D, d-m-Y g:ia', strtotime($rowb['created'])) ."
      </td>
      <td>
        {$rowb['cnt']}
      </td>
      <td id='{$row['id']}'>PENDING</td>
    </tr>";
}

echo $table;

​