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();
?>
2
Answers
You have to do it with aliases :
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
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.