如何内部连接两个表以获得相同的Colum名称php MySQL

Question

我有三张像这张照片的桌子：

table-1 : topic
+-------+-----------+-----------+-------------------+
|   id  |   name    |    time   |       data        |
+-------+-----------+-----------+-------------------+
|   1   |   John    |     1     |   214-444-1234    |
|   2   |   Mary    |     1     |   555-111-1234    |
|   3   |   Jeff    |     1     |   214-222-1234    |
|   4   |   Bill    |     1     |   817-333-1234    |
|   5   |   Bob     |     1     |   214-555-1234    |
+-------+-----------+-----------+-------------------+

table-2 : image
+-------+-----------+-----------+-------------------+
|   id  |   name    |   image   |       data        |
+-------+-----------+-----------+-------------------+
|   1   |   John    |     png   |   214-444-1234    |
|   2   |   Mary    |     png   |   555-111-1234    |
|   3   |   Jeff    |     png   |   214-222-1234    |
|   4   |   Bill    |     png   |   817-333-1234    |
|   5   |   Bob     |     png   |   214-555-1234    |
+-------+-----------+-----------+-------------------+

table-3 : others
+-------+-----------+-----------+-------------------+
|   id  |   name    |   image   |       data        |
+-------+-----------+-----------+-------------------+
|   1   |   John    |     png   |   214-444-1234    |
|   2   |   Mary    |     png   |   555-111-1234    |
|   3   |   Jeff    |     png   |   214-222-1234    |
|   4   |   Bill    |     png   |   817-333-1234    |
|   5   |   Bob     |     png   |   214-555-1234    |
+-------+-----------+-----------+-------------------+

我需要从表1、表2、表3获取所有数据，但我对此有问题。我尝试使用内部连接或左连接，但正如您在上面的示例中看到的，我在两个表(图像列)中有相同的名称colum，所以当我做内部连接时，我只得到一个colum映像。

如何给一个列(图像)一个不同的名称来获得它的内部连接？

我的代码；

<?php
require_once 'con.php';

$id=$_GET['id'];

$sql= "SELECT  * FROM topics // table-1
            LEFT JOIN Image ON topics.id = Image.POSTID // table-2
            LEFT JOIN Category  ON topics.IDCategory = Category.idMainCat // table-3
        where topics.id = ?";

$stmt = $con->prepare($sql); 
$stmt->bind_param("s",$id);
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows >0) {
     while($row[] = $result->fetch_assoc()) {
         $item = $row;
         $json = json_encode($item, JSON_NUMERIC_CHECK);
     }
} else {
    $json = json_encode(["result" => "No Data Foun"]);
}
echo $json;
$con->close();
?>

Answer 1

首先，我建议在您的情况下注意MySQL关键词与保留词，例如time。如果可能的话，它应该在背面或者重命名。

第二，SELECT * --这绝不是一个好主意，只过滤你真正需要的列。

根据问题，你应该使用别名。我在表名和列名上使用别名来区别彼此。

select t.id as topic_id,
       t.name as topic_name,
       t.time as topic_time,
       t.data as topic_data,
       i.id as image_id,
       i.name as image_name,
       i.image as image_image,
       i.data as image_data,
       o.id as others_id,
       o.name as others_name,
       o.image as others_image,
       o.data as others_data
from topic t
left join image i on t.data=i.data
left join others o on o.data=t.data
where t.id=5  ;

https://dbfiddle.uk/LC4nSyAK

请注意。您可以在联接类型INNER/LEFT之间进行选择，我在上面的示例中使用了LEFT，并选择data作为联接列。

Answer 2

你必须用化名来做：

$sql = "SELECT  topics.*, Image.id as image_id, Image.name as image_name, Image.image as image_image, Category.id as category_id, Category.name as category_name, Category.image as category_image, Category.date as category_date 
FROM topics // table-1
LEFT JOIN Image ON topics.id = Image.POSTID // table-2
LEFT JOIN Category  ON topics.IDCategory = Category.idMainCat // table-3
where topics.id = ?";