PHP从另一个表中获取信息

Question

How can I get a row's info from another table without having to SELECT each loop?

//get posts from "posts" table
$stmt = $dbh->prepare("SELECT * FROM posts WHERE user_id = :user_id");
$stmt->bindParam(':user_id', $userId);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    echo $row['post'];

    //get poster's full name from "users" table
    $stmt = $dbh->prepare("SELECT * FROM users WHERE user_id = :user_id");
    $stmt->bindParam(':user_id', $row['poster_id']);
    $stmt->execute();
    $result2 = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($result2 as $row2) {
        echo $row2['full_name'];
    }
}

how can I make this code more efficient and faster?

imagine if i have 1000 posts and each is posted by a different user. i need to get the full name of that user that posted the post. right now, i need to SELECT 1000 times because of those 1000 users. it seems so inefficient right now. how can i make it better?

I heard join might work? what are the solutions?

Answer 1

SELECT * FROM posts
JOIN users ON posts.user_id = users.id
WHERE posts.user_id = :user_id.

You are right, joining the users table onto your posts query will be faster.

Something else you can do to increase performance is to cache the results of your query in something like memcache and then clear the cache when a post is added or deleted. That way you don't need to hit your db every time this data is needed.