Member Avatar for OsaMasw

Hello buddies , I have no Idea what is subquery, every time I tried to link two tables in one sql query I failed
here is the scenario I want:
in this pic I have posts with "user_id" only I need to show the username which stored on another tables called users
I've tried using but its not working.

$sq= "SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM posts WHERE user_id= '".$id."')";

posts table
111111
222222
this is the users tables

  • 3 Contributors
  • 7 Replies
  • 971 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by simplypixie

Dani AI

Generated

A concise expert note tying the earlier replies together and adding practical tips.

A single JOIN is the simplest, most efficient way to retrieve post rows together with the author name; as pointed out, JOINs avoid the N+1-query pattern that demonstrated with per-user subqueries. Use INNER JOIN when only rows with matching users are desired, or LEFT JOIN when posts should appear even if the user row is missing.

Important practical points: make sure users.id and posts.user_id use the same numeric type and both are indexed (an index on posts.user_id speeds joins a lot). Avoid SELECT * — select only the columns needed. Use EXPLAIN to inspect the query plan if results are slow or empty. Also avoid the old mysql_* API; prefer parameterized queries with PDO or mysqli to prevent injection and to work on current PHP versions.

Example (PDO, parameterized, different style than earlier snippets):

$stmt = $pdo->prepare(
  "SELECT posts.id AS post_id, posts.title, users.username AS author
   FROM posts
   LEFT JOIN users ON users.id = posts.user_id
   WHERE posts.user_id = :uid
   ORDER BY posts.created_at DESC
   LIMIT 20"
);
$stmt->execute([':uid' => $id]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['author']." - ".$row['title']."\n";
}

Troubleshooting checklist: confirm $id is set and an integer, verify matching data values in both tables, run EXPLAIN to confirm an index is used, and switch to a JOIN-based single query when returning many rows instead of issuing one query per user.

Recommended Answers

All 7 Replies

Member Avatar for blocblue

You need to use a join. If you only want results where they appear in both tables, you need an inner join.

I'm not sure why you need to query both tables when you're passing the user id to the query. You could simply select the data directly from the users table using the id.

However, please find an example join query below:

SELECT `u`.`id`, `u`.`username`
FROM `users` `u`
INNER JOIN `posts` `p` ON (`p`.`user_id` = `u`.`id`)
WHERE `u`.`id` = 22
commented: Thats really helped +0
commented: Nice Answer! +7
Member Avatar for OsaMasw

Thanks for reply, I need to learn how to join two tables, especially if I have "like" table and "comments" table, so I asked about subquery , anyway thanks for help.

Member Avatar for simplypixie

If you really want to use sub-queries, and presuming you want to show all posts by each user, then this is the sort of thing you will need (I don't know how you want to layout on the page or what your columns are named in your table so have guessed and you will have to adjust accordingly)

$sq= mysql_query("SELECT user_id, username FROM users");

while ($user = mysql_fetch_array($sq)) {
    $sq2 = mysql_query("SELECT * FROM posts WHERE user_id='".$user['user_id']."'");

    echo '<p>'.$user['username'].'</p>';

    while ($posts = mysql_fetch_array($sq2)) {
        echo '<p>'.$posts['title'].'</p>';
        echo '<p>'.$posts['content'].'</p>';
    }
}

If you want to only pull users that actually have posts then combine what you already have with what I have put

$sq= mysql_query("SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM posts)");
Member Avatar for blocblue

- why would you use N + 1 queries to find all users and their posts, rather than use a single query?

SELECT p.*, u.username
FROM posts p
INNER JOIN users u ON (u.id = p.user_id)
Member Avatar for simplypixie

- I wouldn't but the OP wanted to know how to do sub-queries as hasn't learnt about joins yet and I am just trying to help them do what they want to do at the moment (espeically if they can't do a sub-query yet then they are really going to struggle with joins).

Member Avatar for OsaMasw

Thanks for the simplify the operation Mr. , its really helpful to understand how subquery works, I'll leave ths question open if anyone need help in this subject.

Member Avatar for simplypixie

Glad I could help, but please mark as Solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.