Matthew  Warburton Matthew Warburton - 1 month ago 6
PHP Question

SQL statement inside while loop PHP

I am trying to run a SQL statement within a while loop, using the variable $id set in the previous statement but am struggling to get it working. If I remove the statement in the while loop I can see the while loop is functioning as it displays the $id variable multiple times:

$businessPark = $_SESSION['businessPark'];
$num = "1";
$stmt = $conn->prepare("SELECT CompanyId from Portal.services WHERE ".$businessPark." = ?");
$stmt->bind_param("s", $num);
$stmt->execute();
$stmt->bind_result($id);

while ($stmt->fetch()) {

echo "ID: " . $id . "<br>";

}


However when I add the SQL statement back in, I am presented with only the first $id result. If I add in $stmt->close(); at the start of the while loop I do get the first company name, but then the while loops ends. Here is the code:

$businessPark = $_SESSION['businessPark'];
$num = "1";
$stmt = $conn->prepare("SELECT CompanyId from Portal.services WHERE ".$businessPark." = ?");
$stmt->bind_param("s", $num);
$stmt->execute();
$stmt->bind_result($id);

while ($stmt->fetch()) {

$sql = $conn->prepare("SELECT CompanyName from phpipam.ipaddresses WHERE id = ?");
$sql->bind_param("s", $id);
$sql->execute();
$sql->bind_result($CompanyName);
$sql->fetch();
echo $CompanyName;
}


Any ideas please?

Update: If I add in a store result before the loop and free result inside the loop I get the first company name and also get the "finished loop" echo:

$businessPark = $_SESSION['businessPark'];
$num = "1";
$stmt = $conn->prepare("SELECT CompanyId from Portal.services WHERE ".$businessPark." = ?");
$stmt->bind_param("s", $num);
$stmt->execute();
$stmt->bind_result($id);
$stmt->store_result();
while ($stmt->fetch()) {
$stmt->free_result();
$sql = $conn->prepare("SELECT CompanyName from phpipam.ipaddresses WHERE id = ?");
$sql->bind_param("s", $id);
$sql->execute();
$sql->bind_result($CompanyName);
$sql->fetch();
echo $CompanyName;

}

echo "finished the loop";

}


Thanks.

Answer

I had it working (albeit with different queries) on my test server - I'm pretty sure the issue is that you need to pass the resultset through to PHP so that you can prepare the second statement (which must be outside the loop) - otherwise sql = $conn->prepare( ... ); fails and returns false.

This should work:

$businessPark = $_SESSION['businessPark'];
$num = "1";

//first statement
$stmt = $conn->prepare("SELECT CompanyId from Portal.services WHERE ".$businessPark."  = ?");
$stmt->bind_param("s", $num);
$stmt->execute(); 
$stmt->bind_result($id);

//pass the result to PHP so you can prepare a new statement
$stmt->store_result();

//second statement
$sql = $conn->prepare("SELECT CompanyName from phpipam.ipaddresses WHERE id = ?");

while ($stmt->fetch()) {
    $sql->bind_param("s", $id);
    $sql->execute(); 
    $sql->bind_result($CompanyName);
    $sql->fetch();

    echo $CompanyName;
}

//clean up
$stmt->free_result();
$stmt->close();
Comments