PHP query to Join multiple Table rows

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?

@user876345 yes but I do want to create an extra field "uf.user" and assign that user into it too :) — Muhammad Umar
– Muhammad Umar, Commented Dec 11, 2013 at 7:05
Look each post has its own user, i don't want to waste time on quering each post to fetch its own user, i want to make it efficent so may be one query can fetch post and add a field in it "user" and fetch the user which added the post and attach to its "user" field — Muhammad Umar
– Muhammad Umar, Commented Dec 11, 2013 at 7:20

garbetjie · Accepted Answer · 2013-12-11 08:14:10Z

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.

Collectives™ on Stack Overflow

PHP query to Join multiple Table rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related