php - 加入三个没有通用ID的表#MySQL

我有3个表cam_details，cam_category和upload_data如下。

cam_details：

+---------+-----------+-----------
| cam_id | category_id| cam_name |
+========+============+===========
| 1      |  1         | CCTV     |
+--------+------------+-----------
| 2      |  1         | CCtv2    |
+--------+------------+===========
| 3      |  2         | cctv3    |
+--------+------------+===========
| 4      |  4         | cctv4    |
+--------+------------+===========

cam_category：

+-------------+---------------+
| category_id | category_name |
+=============+===============+
| 1           |  Analog       |
+-------------+---------------+
| 2           |  Digital      |
+-------------+---------------+
| 3           |  Network      |
+-------------+---------------+
| 4           |  Simple       |
+-------------+---------------+

upload_data：

+---------+-----------+
| cam_id  | FILE_NAME |
+========+============+
| 1      |  abc.jpg   |
+--------+------------+
| 1      |  abc2.jpg  |
+--------+------------+
| 1      |  abc3.jpg  |
+--------+------------+
| 2      |  xyz.jpg   |
+--------+------------+

现在，我想获取数组中凸轮的详细信息并显示结果。我能够获取cam pic和cam_details，但不能获取cam_category名称。
下面的代码...

<?php
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($conn,"SELECT cam_details.*, upload_data.FILE_NAME FROM `cam_details`
 JOIN  upload_data on cam_details.cam_id = upload_data.cam_id LEFT JOIN cam_category c
    on cam_details.category_id = c.category_id
    GROUP BY upload_data.cam_id ORDER BY cam_id DESC");
while($row = mysqli_fetch_array($result))
{?>

最佳答案

而不是左连接，只连接并首先为cateroy表使用selec命令...

<?php
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($conn,"SELECT cam_details.*, upload_data.FILE_NAME, cam_category.*  FROM `cam_details`
 JOIN  upload_data on cam_details.cam_id = upload_data.cam_id JOIN cam_category
     on cam_details.category_id = cam_category.category_id
    GROUP BY upload_data.cam_id ORDER BY cam_id DESC");
while($row = mysqli_fetch_array($result))
{?>

关于php - 加入三个没有通用ID的表#MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39430692/