Harian Manda Harian Manda - 19 days ago 5
MySQL Question

SELECT multiple tables MYSQL in PHP

So this code works only if the tables that I am calling to match the request, however I still want to display all values of the main table which is the news n table. What is the best way to approach this

Here I have just finished my query

$query="SELECT * FROM news n,category c, comments a, appusers u, admins w WHERE n.cat_id=c.category_id AND w.userId=n.post_author AND a.post_id=n.id AND u.user_id=a.userID ORDER BY a.commentid DESC, n.id DESC";
$result = mysql_query($query);

$json_response = array();
while($row=mysql_fetch_array($result)) {

if (!isset($json_response[ $row['id'] ])) {
$json_response[ $row['id'] ] = [
'id' => $row['id'],
'title' => $row['title'],
'catId' => $row['cat_id'],
'catName' => $row['category_name'],
'catImage' => $row['category_image'],
'postDate' => $row['post_date'],
'postImage' => $row['post_image'],
'post' => $row['post'],
'commentCount' => $row['comment_count'],
'videoUrl' => $row['video_url'],
'tags' => $row['tags'],
'author' => $row['tags'],
'comments' => [],
];
}
$json_response[ $row['id']]['comments'][] = [
'id' => $row['commentid'],
'comment' => $row['comment'],
'name' => $row['user_name'],
'userId' => $row['userID']
];
}

$data = [];
foreach ($json_response as $element) {
$data[] = $element;
}


echo json_encode($data, JSON_PRETTY_PRINT);


And then I try to display the JSON Result here

Answer

Please try below query,This may work for you.

select * from news n
LEFT JOIN category c ON c.category_id = n.cat_id
LEFT JOIN admins w  ON w.userId=n.post_author
LEFT JOIN comments a ON a.post_id=n.id
LEFT JOIN appusers u ON u.user_id=a.userID 
ORDER BY 
a.commentid DESC, n.id DESC"; 
Comments