Help with mysqli and multiple queries

Question

I've never had to use mysqli until today and cannot seem to get this right. What I want is a simple function that will accept a mysql procedure call and have it return those results.

When I started using these procedures, I noticed that the old way of querying the database, using mysql_query, would no longer get me the expected results; one procedure would successfully return and the other would not. After reading the manual and several other examples out there, I found that the reason for this odd behavior was because the results need to be buffered then cleared. I have tried several ways of doing this and have been unsuccessful.

What I have so far works if I create another instance of the mysqli object and will get me both results however, I don't think it's right that I should have to instantiate 20 different objects to get back 20 different queries.

Again, what I want here, is to have a single function that I can feed a procedure to and have the results returned back.

$mysqli = new mysqli('host','user','password','test');
$rs = $mysqli->query('CALL titles()');
while($row = $rs->fetch_object())
{
  print_r($row);
}

---

$mysqli2 = new mysqli('host','user','password','test');
$rs2 = $mysqli2->query('CALL colours()');
while($row2 = $rs2->fetch_object())
{
  print_r($row2);
}

Answer 1

you can make a class for this:

db.php // A db class. Call this you perform a query.

<?php       
    class MyConnection{ 
        var $db_host = 'Localhost';
        var $db_user = 'mYUserName';
        var $db_password = 'myPassword';
        var $db_name = 'mYDB';
        var $connection;


        function Connect(){
            $this->connection = @mysqli_connect($this->db_host, $this->db_user, $this->db_password) 
            or 
            die("Error: ".mysqli_connect_error());
            mysqli_select_db($this->connection, $this->db_name);
            mysqli_query($this->connection, "SET NAMES 'utf8'");
        }

        function Disconnect(){
            mysqli_close($this->connection);
        }

        function ExecSQL($query){
            $result = mysqli_query($this->connection, $query)
                                or
                                die('Error: '.mysqli_error($this->connection));
            return $result;
        }   

    }

?>

Implementation:

include "db.php";
$conn = new MyConnection;
$conn->Connect();

$rs = $conn->ExecSQL('CALL titles()');
while($row = $rs->fetch_object())
{
  print_r($row);
}

$rs2 = $conn->ExecSQL('CALL colours()');
while($row2 = $rs2->fetch_object())
{
  print_r($row2);
}
 $conn->Disconnect();