CODEWITHSUNDEEP
phpsqlloopswhile-loop
Matthew Warburton
Matthew Warburton

Reputation: 5

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.

Upvotes: 0

Views: 1135

Answers (3)

CD001
CD001

Reputation: 8472

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

Upvotes: 0

simon
simon

Reputation: 2946

You can accomplish what you want with a join. I know that this does not answer why your code is not working but in my opinion it's a better solution anyway. 

$businessPark = $_SESSION['businessPark'];
$num = "1";
$stmt = $conn->prepare("
SELECT t2.CompanyName
FROM Portal.services t1
INNER JOIN phpipam.ipaddresses t2 ON t1.CompanyId = t2.id
WHERE " . $businessPark . " = ?
");
$stmt->bind_param("s", $num);
$stmt->execute(); 
$stmt->bind_result($companyName);

More information about join syntax

Upvotes: 0

user7041082
user7041082

Reputation:

Cant comment so answering here.

I think you need to use $stmt->bind_param("s", $businessPark); instead of $stmt->bind_param("s", $num);

Upvotes: 1

Related Questions