Not looping from 2 tables

Question

$sth = $db->prepare("SELECT * FROM users, profile_post WHERE id='$id'");
$sth->execute();
$row = $sth->fetch(PDO::FETCH_ASSOC);
$stuff = array('Published by' => $row['firstname']. " " .$row['lastname'], 'Content profile:' => $row["content"]);
foreach ($stuff as $key => $value) {
    echo "$key: $value\n";
}

This code it's not looping, i've been testing this code with return and arrays

How can i fix it? It just shows one result, I want all of them from the db.

score 1 · Answer 1 · answered Feb 20 '21 at 11:18

You're only retrieving one result:

$row = $sth->fetch(PDO::FETCH_ASSOC);

This is the only place where you fetch anything (in this case, a single row) from the database.

Your foreach is looping over the $stuff array, which you have defined with content from $row - and it only contains the Published by and the Content profile: keys.

Instead you want to iterate over the actual fetch call:

$sth = $db->prepare("SELECT * FROM users, profile_post WHERE id='$id'");
$sth->execute();

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    // process each row as you need here
    var_dump($row);
}

.. should give you all the rows. BUT. There are two other issue here.

Your SQL query doesn't have a join condition, so you're effectively cross joining users with profile_post, which means that every row in users is joined with every row in profile_post. The total number of rows will the become rows in users * rows in profile_post - this is probably not what you want.

You add the join condition by adding profile_post.user_id = users.id to your WHERE condition.

The second issue is that you're using ->prepare, but you're still using $id when building the query itself. You want to use a placeholder and then give the value when you're executing the statement, supplying the value externally.

$sth = $db->prepare("
    SELECT 
        * 
    FROM 
        users, profile_post 
    WHERE 
        profile_post.user_id = users.id AND
        users.id = :id
");

$sth->execute([':id' => $id]);

Not looping from 2 tables

1 Answers1