SQL statement inside while loop PHP

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.

It looks to me like you are trying to access 2 different databases using the same connection? Is that what you are doing — RiggsFolly
– RiggsFolly, Commented Oct 27, 2016 at 8:58
@JYoThI not technically true - you can either reference the schema directly or use mysqli::select_db() to chop and change - different database servers however. — CD001
– CD001, Commented Oct 27, 2016 at 9:02
I don't see that you've set the ID variable, so maybe it's being set somewhere else? — Jack Hales
– Jack Hales, Commented Oct 27, 2016 at 9:03
I'm still trying to figure out why this hasn't just fallen over with a commands out of synch error personally... I think I need more coffee. — CD001
– CD001, Commented Oct 27, 2016 at 9:03

user7041082 · Accepted Answer · 2016-10-27 08:56:36Z

1

Cant comment so answering here.

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

answered Oct 27, 2016 at 8:56

user7041082

Sign up to request clarification or add additional context in comments.

1 Comment

RiggsFolly Over a year ago

I dont think this is right, he uses $businessPark to key the first select which gets him the id he want to use in the second select

CD001 · Accepted Answer · 2016-10-27 09:46:20Z

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

simon · Accepted Answer · 2016-10-27 09:46:06Z

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

Collectives™ on Stack Overflow

SQL statement inside while loop PHP

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related