循环PHP里面的SQL语句

Question

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 1

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();