Using sql queries in a loop

Question

I have a query in my foreach loop and I feel I should not be doing this.

How would I go about improving this code(Here I select Messages):

try{
    $sql="
    SELECT * FROM messages 
        WHERE workspace_id=:project_id
        ORDER BY message_created DESC
        LIMIT 20 
        OFFSET :offset";
    $stmt=$db->prepare($sql);
    $stmt->bindValue(':project_id', $project_id, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $messages=$stmt->fetchAll(PDO::FETCH_ASSOC);
    }catch(Exception $e){echo $e->getMessage();}

Now I need users first and last name from table users. I couldn't think of another way but putting it in a loop.

foreach($messages as $row){ 
echo $row['message'];

$sql=  "SELECT first_name, last_name FROM users 
        WHERE user_id=:id 
        LIMIT 1";
        $stmt=$db->prepare($sql);
        $stmt->bindValue(':id', $row['sent_by']);
        $stmt->execute();
        $user=$stmt->fetch(PDO::FETCH_ASSOC);

echo $user['first_name'] . "&nbsp;" . substr($user['last_name'],0,1) . ".";
}

This code works just the way I want it to but it doesn't look like it is supposed to be done this way.

Can anyone help me out here

Are you trying to get the user associated with the message specifically? If so, we can just do a join on the sent_by matching the id column from users. like this: LEFT JOIN users on users.id = messages.sent_by — Ohgodwhy
– Ohgodwhy, Commented Nov 25, 2015 at 17:15
Thanks! Works great! But now I have to list all the columns to select because user has over 30 columns and I just needed two. Well, it's much better to do it this way then the loop — Ikhlak S.
– Ikhlak S., Commented Nov 25, 2015 at 17:25

cyber.sh · Accepted Answer · 2015-11-25 17:21:49Z

7

You could do a SQL join to completely avoid the foreach loop.

  try{
        $sql="
        SELECT messages.*, users.first_name, users.last_name
          FROM messages join users on messages.user_id = users.user_id
            WHERE workspace_id=:project_id
            ORDER BY message_created DESC
            LIMIT 20 
            OFFSET :offset";
        $stmt=$db->prepare($sql);
        $stmt->bindValue(':project_id', $project_id, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $messages=$stmt->fetchAll(PDO::FETCH_ASSOC);
        }catch(Exception $e){echo $e->getMessage();}

Note: I am assuming messages table has user id and is stored in "user_id" field. Change the field name accordingly, if it is different.

answered Nov 25, 2015 at 17:21

cyber.sh

7324 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GordonM Over a year ago

Looking at the original code I think the column for user ID in his messages table is called sent_by. Otherwise this is the correct approach.

Collectives™ on Stack Overflow

Using sql queries in a loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related