CODEWITHSUNDEEP
phpmysqlloopspdoforeach
Donut
Donut

Reputation: 167

How to nest 2 SQL statements with a foreach loop inside each other

Okay I try with the first query to get all names of the computers from the table psComputers. Now I need in the second query a variable from the first query to iterate over all entries which are assigned to the respective computer in the table psTest. I wonder if such a thing is possible at all?

Table psComputer contains ID, name
Table psTest contains ID, computername, category, value

index.php

$statement = $pdo->prepare("SELECT * FROM psComputers ");
$statement->execute();
$result = $statement->fetchAll();
if ($statement->rowCount() > 0) {
    foreach ($statement->fetchAll() as $row) {
        $id = $row['ID'];
        $name = $row['name'];

        $statement2 = $pdo->prepare("SELECT * FROM psTest WHERE computerName = $name");
        $statement2->execute();
        $result2 = $statement2->fetchAll();
        if ($statement2->rowCount() > 0) {
            foreach ($statement2->fetchAll() as $row2) {
                $id2 = $row2['ID'];
                $computerName = $row2['computerName'];
                $category = $row2['category'];
                $value = $row2['value'];
            }
        }
    }
}

Upvotes: 0

Views: 303

Answers (2)

Qirel
Qirel

Reputation: 26450

A couple things to note,

  1. When using strings in queries, they must be quoted.
  2. You are already preparing the statement - bind the value instead, and the note above becomes irrelevant.
  3. You can use a JOIN instead of running a query in a loop. This will also remove the variable in the name, making both notes above irrelevant! (You should take note of both, but they become irrelevant for the code in question).

Its rarely a good idea to run a query within a loop.

$statement = $pdo->prepare("SELECT pt.* 
                            FROM psTest pt 
                            JOIN psComputers pc ON pt.computerName=pc.name");
$statement->execute();
$result = $statement->fetchAll();
if (count($result)) {
    foreach ($result as $row) {
        $id2 = $row['ID'];
        $computerName = $row['computerName'];
        $category = $row['category'];
        $value = $row['value'];
    }
}

Upvotes: 3

Barmar
Barmar

Reputation: 780871

You need quotes around $name in the second query, since it's a string.

$statement2 = $pdo->prepare("SELECT * FROM psTest WHERE computerName = '$name'");

But since you're using a prepared query, you should use a parameter instead of substituting a variable.

You also shouldn't call $statement->fetchAll() twice. The first call will read all the rows, and the second won't have anything left to read (it doesn't reset the cursor).

$statement = $pdo->prepare("SELECT * FROM psComputers ");
$statement->execute();
$result = $statement->fetchAll();
if (count($result) > 0) {
    $statement2 = $pdo->prepare("SELECT * FROM psTest WHERE computerName = :name");
    $statement2->bindParam(':name', $name);
    foreach ($result as $row) {
        $id = $row['ID'];
        $name = $row['name'];
        $statement2->execute();
        $result2 = $statement2->fetchAll();
        if (count($result2) > 0) {
            foreach ($result2 as $row2) {
                $id2 = $row2['ID'];
                $computerName = $row2['computerName'];
                $category = $row2['category'];
                $value = $row2['value'];
            }
        }
    }
}

But even better is to just join the two queries:

$statement = $pdo->prepare("
    SELECT c.id AS computerID, c.name AS computerName, t.id AS testID, t.category, t.value
    FROM psComputers AS c
    JOIN psTest AS t ON c.name = t.computerName
    ORDER BY c.id");

Upvotes: 3

Related Questions