PHP查询加入多个表行

Question

I am using following code to query

 $statement = $conn->prepare('SELECT * FROM userFeeds WHERE userId = :userId ORDER BY creationDate ASC LIMIT 100');
                $statement->bindParam(':userId'    , $userId, PDO::PARAM_STR);
                $statement->execute();
                $posts = $statement->fetchAll(PDO::FETCH_ASSOC);

                return array('Success'=>$row, 'Posts'=>$posts);

Each post has following feilds,

id   userId   comment  type  date

I also want to get the UserInfo of each post along with the other fields of posts.

Currently in JSON i am getting above fields, but If i want to add an extra field "user" and pass user to it e.g

foreach ($post in $posts)
{
   // PERFORM A QUERY TO GET USER FROM post=>userId
    $post['user'] = $user;
}

This loop could be a long thing. Can I manage to do something more efficiently or in one query only?

Answer 1

Whenever I've been faced with something similar to this, the code snippet below is an example of how I solve it, and reduce the number of queries:

# build an array of user ids
$userIds = array();
foreach ($posts as $post) {
    if (!in_array($post['userId'], $userIds)) {
        $userIds[] = (int)$post['userId'];
    }
}

# fetch these users.
$st = $conn->query('SELECT * FROM `users` WHERE `id` IN (' . implode(',', $userIds) . ')';
while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
    $users[] = $row;
}

# assign users to posts.
foreach ($posts as $index => $post) {
    $posts[$index]['user'] = null;
    foreach ($users as $user) {
        if ($user['id'] == $post['userId']) {
            $posts[$index]['user'] = $user;
            break;
        }
    }
}

The basic premise of this is that you extract all the relevant user ids, perform a single query to find the relevant users, and then re-assign those users back into the original array.