CODEWITHSUNDEEP
phpmysql
Shivkesh Dwivedi
Shivkesh Dwivedi

Reputation: 17

Facing issues while fetching data from two different mysql tables using a single query..!

I have two tables, one for the posts and another for the users. I am fetching all posts from the 'posts' table and user data (who posted that post) from the tbl_users.

currently, I am using this query :

$query = $pdo->prepare("SELECT * FROM `posts`,`tbl_users` WHERE id = user_id_p ORDER BY `post_id` DESC");
            $query->execute();
            return $query->fetchAll();

it is working fine, it is fetching all the posts from the posts table and user data from tbl_users. However, my issue is this that I don't want to fetch all posts, but I want to fetch only those posts which are posted by the specific user(for example by John only) and the user data for John only from the tbl_user.

(field Id from tbl_users and field user_id_p from the table posts are same in both the tables.)

Any suggestions or help?

Upvotes: 0

Views: 31

Answers (2)

Yisal Khan
Yisal Khan

Reputation: 387

You can use JOIN for this. Example

select P.name, U.name from post P NATURAL JOIN user U;

Upvotes: 0

forpas
forpas

Reputation: 164099

Although your query is working, it is not at all efficient because it uses an implicit cross join which results in a very large resultset.
Use a proper INNER JOIN with a condition applied with WHERE:

SELECT u.*, p.* 
FROM tbl_users u INNER JOIN posts p
ON u.id = p.user_id_p
WHERE u.id = ?
ORDER BY p.post_id DESC

Replace ? with the id of the user.

Upvotes: 1

Related Questions