MySQLi Library with Stored Procedures & Multiple result sets support

[eluser]Atasa[/eluser]
I was very anxious every time a new release of Ci was coming out to see weather if
mysqli driver was more enhanced and complete, and was always tweaking the mysqli_driver to support
the projects of mine that use Stored Procedures, and sometimes wanted to have Multiple Result Sets in one go, instead of writing 2 or more sql queries with the same pattern.
Maybe I am too lazy but I am happy about it.
Therefor one day I decided to write a library to do this and not worry any more about CI system files.

So here it is:

Code:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Mydb
{
    private $CI, $Data, $mysqli;
    
    /**
      * The constructor
    */
    public function __construct()
    {
        $this->CI = & get_instance();
        $this->Data = array();
        $this->mysqli = $this->CI->db->conn_id;
    }
    
    public function Query($sql, $mode="Array")
    {
        $result = @$this->mysqli->query($sql); //assign results to $result.
         switch ($mode)
           {
             case 'Row':
              //use the data in the resultset
              $this->Data = $result->fetch_object();

              break;                            
                        
              default:
                while ($row = $result->fetch_object())
                 {
                   $this->Data[] = $row;                        
                 }
               break;
           }
            
            //free the resultset
            $result->free();
          
            //clear the other result(s) from buffer loop through each result using the next_result() method
            while ($this->mysqli->next_result())
            {
                //free each result.
                $result = $this->mysqli->use_result();
                if ($result instanceof mysqli_result)
                {
                    $result->free();
                }
            }
         return $this->Data;
    }
    
    public function Multi_Query($sql)
    {
       // automatically buffers resultsets and assigns true or false on fail to $query
       $query = @$mysqli->multi_query($sql);
        //$this->query = $sql;
         do
         {
            /* store first result set */
            if ($result = $this->mysqli->store_result())
            {
                $this->Data[] = $result->fetch_object();
                $result->free();
            }
          }
          while ($this->mysqli->next_result());
        # DEBUG   print $sql;
        return $this->Data;
    }          
}
/* End of file Mydb.php */

/* Location: ./system/application/libraries/Mydb.php */

[eluser]Atasa[/eluser]
OK if found out that there is not much use from what i posted before.
So I decided to make a real library OO style at least, and much more useful.
What is missing:
The error handling that the query might return, the multiple results function, a more detailed documentation.
Will come up some time soon.

So here is the library file:

Code:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

// We need some constants to clear out some things also code wise
define('FETCH_ARRAY',1);
define('FETCH_ASSOC',2);
define('FETCH_OBJECT',3);
define('MYSQL_ERROR_PARAMETER',1);
define('MYSQL_ERROR_CONNECT',2);
define('MYSQL_ERROR_SELECT',3);
define('MYSQL_ERROR_SQL',4);

class Mydb
{
    private $CI, $mysqli, $result;
    
    /**
      * The constructor
    */
    public function __construct()
    {
        $this->CI = & get_instance();
        $this->mysqli = $this->CI->db->conn_id;
        //var_dump($this->mysqli);
    }
    
    // run query
    public function Query($query)
    {
        $this->result = @$this->mysqli->query($query);
        while ($this->mysqli->next_result())
        {
          //free each result.
          $res = @$this->mysqli->use_result();
          if ($res instanceof mysqli_result) { $res->free(); }  
        }
        return new Result($this->mysqli,$this->result);
    }
}

class Result

{

    private $mysqli, $result;


    public function __construct(&$mysqli, $result)

    {
        $this->mysqli = &$mysqli;
        $this->result = $result;
    }

    

    public function fetch($fetchMode=FETCH_OBJECT)

    {
        if ($fetchMode == FETCH_ARRAY) { $row = @$this->result->fetch_array(MYSQLI_BOTH); }
        if ($fetchMode == FETCH_ASSOC) { $row = @$this->result->fetch_assoc(); }
        if ($fetchMode == FETCH_OBJECT) { $row = @$this->result->fetch_object(); }

        if ($row) { return $row;}

        elseif ($this->numRows() > 0)
        {
            @$this->result->data_seek(0);
            return array(); //false;
        }

        else { return array();}//false;
    }

    
    public function numRows()     { return @$this->result->num_rows; }

    public function affectedRows()  { return @$this->result->affected_rows;    }

    public function free()             { $this->result->free(); }

    public function MySQLError()     { return @$this->mysqli->error; }

}

Usage:
On your model you could say:

Code:

$this->load->library('mydb');
$sql = "CALL myFunction()";
$result = $this->mydb->Query($sql);
while($row = $result->fetch(FETCH_OBJECT)) { $data[] = $row; }
$result->free();
return $data;

I think that it is straight forward, simple, plus the library itself doesn't do anything
more with result then just give it to you, which I like better.

Cheers
A.

[eluser][email protected][/eluser]
I really like CI but mulple resultsets support is missing! OMFG!

[eluser][email protected][/eluser]
My little contribution to the CI:

DB_result.php

Code:

function next_result() { return FALSE; }

mysqli_result.php

Code:

/**
     * Prepare next result
     *
     * @return    bool
     */
    function next_result()
    {
        return mysqli_next_result($this->conn_id);
    }

[eluser]Atasa[/eluser]
[quote author="[email protected]" date="1246171949"]I really like CI but mulple resultsets support is missing! OMFG![/quote]
Well I haven't got the time really to go through all way, but I still have the motivation.

[eluser]Tim Brownlaw[/eluser]
I've running version 1.7.2 and guess what?

I've had to modify my mysqli_results.php and DB_result.php file as above, which I've had to do on older versions.
Lucky I knew to go back to my "Already Fixed" version and make the changes to this latest version.

I dunno, is it just a small few that actually use Stored Procedures that constantly come upon this poor forgotten little function that unbreaks your code?


Derek - When are you going to add the function next_result() into CI?

If one client/student upgrades CI on their website that utilizes mysqli = busted website.

Cheers
Tim

[eluser]Tim Brownlaw[/eluser]
Well I'm now into returning multiple result sets back from my Stored Procedures.

The changes I had to make to mysqli_result.php work just dandy on my development PC.
It's when I put the site up onto my hosting account that things went upside down.

The calls to the Stored Procedures only return the 1st result set and ignore everything else.

Don't you hate that! It's kind of put my project into screaming halt mode!

So I wrote an extensive set of test scripts (using native PHP ) with installer for the demo tables and Stored Procedures up on the same hosting and it works a treat.

Native PHP = Works - Local and Host
CI Implementation - Work locally, Don't work on the Host.

The sample native PHP Code is up at http://www.webatories.com/testsp

I'll put up the CI changes as soon as I recover from my extensive messing around in the code and
provide the complete code for download when I get the chance.

I could go and try another framework but I'd rather contribute to CI as I kind of like even with it shortcomings. That's the whole point of this forum. To help make CI better.

Cheers
Tim Brownlaw

[eluser]Atasa[/eluser]
I would never touch any CI core libraries, it is very sad then when you want to upgrade to a new version.
But the limitation of CI is not for multiple results only. you can't really call a stored proc in the first place.

[eluser]Tim Brownlaw[/eluser]
[quote author="Atasa" date="1270475338"]I would never touch any CI core libraries, it is very sad then when you want to upgrade to a new version.
[/quote]

That's a valid argument which I've also stated in the past.

I decided it was simpler ( in the end ) to just put the required changes into the neccessary "core" files.


Cheers
Tim

[eluser]binbink[/eluser]
the mentioned solution works fine for me when the procedure returns multiple rows.

$result = $this->mydb->Query($sql);

but what if routine does not return any data? e.g. it only updates two tables with the specified data.

if I use $this->db->query I see

Error Number: 2014
Commands out of sync; you can't run this command now

if I use

$this->mydb->Query($sql);

I do not see any error messages; but the data is not updated. NOTE: there should not be an issue with routine itself since if I print the query in question and try to execute this in MySQL Workbench - tables are updated correctly.

Let me know if there are any known/possible solutions for this.

Thank you