Create a nested SQL loop

Question

How do I make a nested SQL loop using PHP?

I am trying to output all bundle_id's from the table packages where the row user (primary key) is $username. Inside that loop, I want to run another loop to output blacklist from the table access where bundle_id (not primary key) is the output from the first loop.

Here is my code:

$sql = "SELECT bundle_id FROM packages WHERE user='" . $username . "'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
    echo $row['bundle_id'];
    $sql = "SELECT blacklist FROM access WHERE bundle_id='" . $row['bundle_id'] . "'";
    $result = mysqli_query($conn, $sql);
    while($row = mysqli_fetch_assoc($result)){
        echo $row['blacklist'];
    }
}

The issue with my code is that it only outputs the first row from the table access.

[How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — brombeer, Apr 17 '20 at 13:58
@kerbholz When I change it, the page endlessly loads and eventually times out — Parsa Yazdani, Apr 17 '20 at 14:03

score 3 · Answer 1 · answered Apr 17 '20 at 14:00

3

You're reusing the same local variable $row and $result in both loops, which is why you're not getting the correct results. But much better, use a JOIN:

SELECT a.blacklist
FROM access a
JOIN packages p ON a.bundle_id = p.bundle_id
WHERE p.user = ?

Notice, I'm using a bind variable to prevent SQL injection / syntax errors, etc.

answered Apr 17 '20 at 14:00

Lukas Eder

211,314
129
689
1,509

How would I implement that in my script? – Parsa Yazdani Apr 17 '20 at 14:14
2

@ParsaYazdani: See https://www.php.net/manual/en/mysqli-stmt.bind-param.php – Lukas Eder Apr 17 '20 at 14:22

Create a nested SQL loop

1 Answers1