SELECT multiple tables MYSQL in PHP

Question

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

first and foremost: stop using mysql_-functions, they are deprecated and in PHP7.0, removed. use mysqli_ or PDO instead. second: you should really uniformize your table naming convention. category_id, userID, userId? three different schemes for ID-naming? that's bound to introduce errors. third: do you really want to select all data with every statement you run, without any filtering or reduction? that's gonna slow your system down terribly after a while. — Franz Gleichmann
– Franz Gleichmann, Commented Nov 17, 2016 at 7:56

hetal gohel · Accepted Answer · 2016-11-17 07:56:33Z

1

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

answered Nov 17, 2016 at 7:56

hetal gohel

788 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SELECT multiple tables MYSQL in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related