Join comments.user_id to users.id, then filter with comments.post_id. Execute the statement as a prepared query and fetch every returned row in a loop so each comment is rendered with its username.
The relationship between the two tables
In the SitePoint Forums question from August 2, 2021, the tables were described as follows:
comments:id,comment,post_id,user_id,created_atusers:id,username
user_id is the foreign-key value that identifies the author. post_id has a different job: it limits the result to comments belonging to one blog post.
SQL query for comments and usernames
SELECT comments.id,
comments.comment,
comments.created_at,
users.id AS user_id,
users.username
FROM comments
INNER JOIN users ON comments.user_id = users.id
WHERE comments.post_id = ?
ORDER BY comments.created_at;The question mark is a prepared-statement placeholder, not a value to concatenate into SQL. Qualifying each column with its table name avoids ambiguity because both tables contain an id column. The aliases make the returned associative-array keys explicit.
#1 Best Overall
Choosing the join type
| Join | Result | Use it when |
|---|---|---|
INNER JOIN |
Returns only comments with a matching row in users. |
Every displayed comment must have an existing author record. |
LEFT JOIN |
Returns every matching comment, even when its user row is missing; user columns are NULL for an unmatched author. |
Orphaned comments must remain visible and the application can provide a fallback name. |
The join choice depends on how the application should handle a missing user record. It does not change the purpose of the post_id filter.
Executing the query with mysqli
$stmt = $link->prepare(
'SELECT comments.id, comments.comment, comments.created_at,
users.id AS user_id, users.username
FROM comments
INNER JOIN users ON comments.user_id = users.id
WHERE comments.post_id = ?
ORDER BY comments.created_at'
);
$stmt->bind_param('i', $post_id);
$stmt->execute();
$result = $stmt->get_result();
while ($comment = $result->fetch_assoc()) {
// Render this comment and $comment['username'].
}This is an illustrative pattern based on the schema in the forum thread, not a tested drop-in script. Validate $post_id according to your application, check preparation and execution errors, and use the result API supported by the mysqli/driver configuration on your server.
Why a loop is required
A post can have multiple comments. Calling fetch_assoc() once retrieves only the first row; the while loop calls it repeatedly until no rows remain. The original thread included a single fetch, and a reply identified that as the reason only one comment was displayed.
Common mistakes to avoid
Confusing the join key and the post filter
Use comments.user_id = users.id in the join. Use comments.post_id = ? in WHERE to select one post. Replacing one with the other cannot identify the author correctly.
Free tools Windows power users keep installed
One-click scans. No signup required.
Building SQL with an interpolated ID
Bind the post identifier with a prepared statement, as in bind_param('i', $post_id), rather than inserting request data into the SQL string.
Suppressing diagnostics
The forum code used error_reporting(0). During development, suppressing all reporting can hide failed preparation, execution, or result handling. Log or otherwise handle database errors using the application’s normal error policy.
Rank #4
Rendering unescaped user content
Comments and usernames are user-generated values. Escape them for the HTML context in which they are output; SQL parameterization protects the query, while output escaping protects the rendered page.
Assuming the date issue has a known fix
The thread ended with the poster saying the problem was fixed, but it did not identify what changed. The available discussion therefore does not establish a particular correction for created_at display. Inspect the stored type, timezone handling, and formatting code in the actual application.
Best Value
Practical checklist
- Confirm that
comments.user_idstores the corresponding value fromusers.id. - Join on the user columns and filter on the post column.
- Select explicit, qualified columns and alias duplicate names.
- Bind the post ID instead of concatenating it into SQL.
- Fetch rows in a loop.
- Choose
INNER JOINorLEFT JOINaccording to the required behavior for missing users. - Handle database errors and escape comment and username output.
What the original thread establishes
The SitePoint discussion supplies the schema, the join-and-filter pattern, the need to fetch multiple rows, and advice to use prepared statements. It does not provide a complete application, a verified execution, or a documented explanation of the poster’s date-format problem. Disqus was mentioned only as an alternative the poster might consider, not as an evaluated solution.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
