Question 113460 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

2

Answers


  1. 0

    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 = ?";
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search