mysql tables: Join to two tables in a request-CodePudding

Newbie here.. Here's what I'm trying to do.

I have a Posts table with the following columns

id, userid, bodyText, date

and I have another table for users users.

When users submit a post, the user-id saves into the "posts" table under "userid". Well, I want to display that users information, such as name and picture, on the posts using the "userid" to get each users information so that their name and picture shows along with the post they submitted.

Sorry if I'm not being clear, english is not my first language and like I said, I'm new at this and still trying to learn. I had it where when they submit the post, their name and picture also saves into the "posts" table but I want to change it because if the user updates their name or picture, it will still show the name and picture they had when they submitted the post. I want their name and picture to update on the posts if they update their information on the users table.

CodePudding user response：

What you are asking about is a JOIN. In general the Join will look like

SELECT p.id. p.bodyText, p.date, u.name, u.picture FROM posts as p INNER JOIN users as u ON p.user_id = u.id where p.id = 123;

This will select the post with the ID 123 from the posts table and joins the users information from the users table based on the user_id columns value from the posts table.

In Syntax this could be something like

$sth = $dbh->prepare("SELECT p.id. p.bodyText, p.date, u.name, u.picture FROM posts as p INNER JOIN users as u ON p.user_id = u.id where p.id = ?");

$sth->execute(array(123));
$red = $sth->fetchAll();