Eucar Eucar - 6 months ago 32
MySQL Question

MySQL select user motto

I'm stucked in this problem and searching a solution for 2 hours.

In fact I have a posts table which contains all comments in my app.

Each post data has a row called ownerid which keeps the id of the owner of this comment.
And it also contains a row called groupid.

What I want to have is to select all posts with a certain groupid (eg. 2).
and the motto from the user which is in users table.

So something like this.

SELECT * FROM posts WHERE groupid = 2


This returns all rows with groupid.

SELECT motto FROM users WHERE userid = $row['ownerid']


But this all in one code, because I'm using while loop to fetch rows.

Then.

$stmt->bind_result($postid, $motto, $groupid, $text, $postdate);


Thanks in advance! :)

Answer

If I undestand correctly your question, you should try

SELECT P.*, U.motto from posts P INNER JOIN users U on P.ownerid = U.userid 
WHERE P.groupid = 2;