Having problems with MySQLi SELECT

[Hobbyist_PHPer]

I'm not really sure what's going on, but I have been replacing all of my mysql queries with mysqli, and so far it is working fine, until I got to this one in particular...

 

I am getting this error... "Warning: mysqli::prepare(): Couldn't fetch mysqli"  and this error... "Warning: mysqli::close(): Couldn't fetch mysqli"

 

I researched that problem, but none of what others had to say about fixing it, applied nor worked for me.

 

What's funny about it is that just above that query, is another query in which has no problems...

 

Here's the top query that works fine:

$sql = "SELECT * FROM Agencies ORDER BY AgencyName ASC";
if ($result = $mysqli -> query($sql))
{
    while ($row = $result -> fetch_assoc())
    {
        echo '<option value="'.$row['AgencyID'].'">'.$row['AgencyName'].'</option>';
    }
    $result -> free();
}
$mysqli -> close();

Now here's the query that is throwing the errors:

$sql = "SELECT AgenciesAgents.*, Agents.AgentFirstName, Agents.AgentLastName
    FROM AgenciesAgents 
    LEFT JOIN Agents ON AgenciesAgents.AgentID = Agents.AgentID
    WHERE AgenciesAgents.AgencyID = ?
    ";
if ($stmt = $mysqli -> prepare($sql))
{
    $stmt->bind_param("i", $AgencyID);
    $stmt->execute();
    while ($row = $stmt -> fetch_assoc())
    {
        echo '<p><span style="vertical-align: top; color: #515151;">'.$row['AgentLastName'].', '.$row['AgentFirstName'].'</span><a href="agency-agent-correlations.php?function=deletecorrelation&agencyid='.$AgencyID.'&agentid='.$row['AgentID'].'" onclick="return confirm(\'Are you certain you wish do delete this agent?\');"> <img src="images/delete_icon.png" width="20" height="20" /></a></p>';
    }
    $stmt -> free();
}
$mysqli -> close();

[Barand]

Stmt objects use the fetch method. The result objects (as in your first query) use fetch_assoc.

[mac_gyver]

and since you are closing the mysqli connection after the first block of code, it is not available later in the program.

[Barand]

So he does - missed that.

 

Only open the connection once at top of script and pass the connection to any functions that need it.

 

It will be closed automatically at end of script. The only time you need to open mid-script is if you change to another server.

[Hobbyist_PHPer]

and since you are closing the mysqli connection after the first block of code, it is not available later in the program.

 

You know, I'm glad you brought that up, because I was thinking the same thing, so how do you go ahead and open it back up, because in the connection file is where it is originally instantiated...

[Hobbyist_PHPer]

So he does - missed that.

 

Only open the connection once at top of script and pass the connection to any functions that need it.

 

It will be closed automatically at end of script. The only time you need to open mid-script is if you change to another server.

 

So what you're saying is, don't close the connection until the end of the page, after all of the queries on that page are completed?

[Barand]

 

It will be closed automatically at end of script.

 

You don't even have to do that

[Hobbyist_PHPer]

On that top query, I use query, but on the other one I use prepare, I'm assuming that if I have variables in the sql, then I use prepare, but if I don't, then I use query, is that correct?

[Barand]

That's the best way. You can use query() with variables but have to escape  them with real_escape_string() to prevent injection attacks

[Hobbyist_PHPer]

Stmt objects use the fetch method. The result objects (as in your first query) use fetch_assoc.

 

For the life of me, I can't seem to figure out the correct syntax, to make this code work...

$sql = "SELECT AgenciesAgents.AgentID, Agents.AgentFirstName, Agents.AgentLastName
    FROM AgenciesAgents 
    LEFT JOIN Agents ON AgenciesAgents.AgentID = Agents.AgentID
    WHERE AgenciesAgents.AgencyID = ?
    ";
if ($stmt = $mysqli -> prepare($sql))
{
    $stmt->bind_param("i", $AgencyID);
    $stmt->execute();
    while ($row = $stmt -> fetch_assoc())
    {
        echo '<p><span style="vertical-align: top; color: #515151;">'.$row['AgentLastName'].', '.$row['AgentFirstName'].'</span><a href="agency-agent-correlations.php?function=deletecorrelation&agencyid='.$AgencyID.'&agentid='.$row['AgentID'].'" onclick="return confirm(\'Are you certain you wish do delete this agent?\');"> <img src="images/delete_icon.png" width="20" height="20" /></a></p>';
    }
    $stmt -> close();
}

[Barand]

have a look at the examples --> http://php.net/manual/en/mysqli-stmt.fetch.php

[Hobbyist_PHPer]

Thanks, I thought about using fetch in that manner, but I couldn't comprehend how I could have multiple row result sets, the way it looks, but sure enough, as it loops, the variable changes each time...

 

Thanks for all of your help, it is much appreciated.