How to inner join two tables to get same Colum name php MySQL-CodePudding

I have 3 tables like this image:

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    |
 ------- ----------- ----------- -------------------

I need to get all data from table-1 and table-2 , table-3 but I have problem with that. I try to use inner join or LEFT JOIN but as you can see into above example I have same name colum in two tables (image column) so When I make to do inner join I get just one colum image.

How to give a column(image) a different name to get it with inner join?

My code;

<?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();
?>

CodePudding user response：

First, I suggest be aware of MySQL Keywords and Reserved Words such as time in your case. It should be inside backticks or rename if possible.

Second , SELECT * it is never a good idea, filter only the columns which you really need.

As per the question you should use alias. I used alias on the table name and column names to distinguish from each other

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

Note. You could choose between the JOINS type INNER/LEFT , I used LEFT on the example above and choose data as the joined column.

CodePudding user response：

You have to do it with aliases :

$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 = ?";