PHP/SQL query table inside SQL loop

Question

I'm try to query another table whilst inside an existing loop. Is this possible?

So i'm taking a variable from the outer loop and trying to use that variable to get data from another table.

The following just wipes out my queries altogether...

            <?php $sql = "SELECT t1.id as messageID, t1.from_id, t2.full_name, t2.title FROM table1 t1 JOIN table2 t2 on t2.id = t1.user_id WHERE t1.user_id = '$userid' AND t1.unread = 0";
            $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) {; $from = $row["from_id"]; $messageID = $row["messageID"]; ?>
            <tr>
                <td><?php echo $row["full_name"];?></td>
                <td><?php echo $row["title"];?></td>
            </tr>
            <?php }; }; ?>

I've tried using $r instead of $row and $rs instead of $result on the inner query. Should that work ok? — onesixty, Apr 17 '20 at 07:48

score 1 · Accepted Answer · answered Apr 17 '20 at 07:55

1

Whenever possible, you should avoid using a SQL within a loop and try and fetch all of the data in one go. In this case you can use JOIN to fetch the user name in the first statement. Personally I would also list just the columns you want to fetch rather than using *...

$sql = "SELECT t1.id as messageID, t1.from_id, t2.full_name, t2.title 
        FROM table1 t1
        JOIN table2 t2 on t2.id = t1.user_id
        WHERE t1.user_id = '$userid' AND t1.unread = 0"; 
$result = $conn->query($sql); 
if ($result->num_rows > 0) { 
    while($row = $result->fetch_assoc()) {; 
        $from = $row["from_id"]; 
        $messageID = $row["messageID"]; 
        ?>
        <tr>
            <td><?php  echo $row['full_name'];?></td>
            <td><?php echo $row["title"];?></td>
        </tr>
        <?php 
    }
}

Not able to test it, but should be more usable.

You should also look into prepared statements as this can help resolve various issues - How can I prevent SQL injection in PHP?

answered Apr 17 '20 at 07:55

Nigel Ren

56,122
11
43
55

Wow thanks Nigel - that makes sense to me and i'll read up on that more. I've edited my code in the question to reflect your suggestion. It doesn't pull any data through from either table but am sure its on the right track. – onesixty Apr 17 '20 at 08:09
You shouldn't alter the original code in your question as it then becomes confusing for others to understand your problem. It's also worth checking what SQL is generated and try and run it in something like PHPMyAdmin so you can see what it generates and debug your code there. – Nigel Ren Apr 17 '20 at 08:11
Ok sorry - two lessons learned today. Thanks for your help again! – onesixty Apr 17 '20 at 08:12
Good luck anyway. A quick source of JOIN explanations is https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins. – Nigel Ren Apr 17 '20 at 08:16
No i got it working perfect and now finally understand how to JOIN just from your answer. Very much appreciated! Have a good day! – onesixty Apr 17 '20 at 08:27

PHP/SQL query table inside SQL loop

1 Answers1