Extend mysqli_stmt add fetchAll for 5.2.17?

Question

php 5.2.17

mysqli prepared statements results bound to variables.

Want to extend mysqli_stmt to add a custom fetchAll method.

Why? Because would like to stop writing the same while statements for prepared statement database results and enclose the code into mysqli itself so it is handled for all calls automatically. I also add my own special code and filters as well.

I have been able to extend mysqli class and add custom processing that does this for non-prepared statements but would like to add this to the prepared statement processing.

Reading through stackoverflow and other places I found some examples of extending mysqli_stmt but nothing similar to adding a fetchAll type of custom method for php 5.2x.

I do not understand how this works, I am somewhat new to OOP and extending core php classes and would appreciate as much explanation as possible so I can learn.

My webhost has php 5.2.17, I have no control over that so upgrading php isn't an option. I created a class that handles PDO, mysqli and mysql database connections. I am trying to optimize this now and encapsulating and moving as much as possible into related areas thus my interest in doing this.

I understand about result class but from what I can tell the handling of fetching prepared statements is done through mysqli_stmt class which only has fetch to retrieve results. I have not been able to get results from within this class, only outside. I assume due to the binding or parameters and variables. I don't understand exactly the process here though.

Also the results I am getting back are bound to variables and these are updated via a while loop. I would like to take this process and place it inside of the stmt class but I do not understand how to do this.

Thank you for any help you can provide. I am pretty smart but I am having difficulty understanding this process so if you can explain it well that would be great.

Thanks.

Edit:

I want to add onto this to show some code.

$mysqli->execute();     
$mysqli->store_result();
$mysqli->bind_result($id, $name, $age);                     

$i = 0;
While($mysqli->fetch()){            
    $array[$i]['id'] = $id;
    $array[$i]['age'] = $age;
    $array[$i]['name'] = $name;
    $i++;
}           

return $array;

Look at the While statement.

Now lets say I want to place that into the MYSQLI_STMT class so that I don't have to write that over and over and over, just let the MYSQL_STMT class deal with it.

That would go into a new method called FetchALL. Problem is, it won't work. Due to this being prepared statement the statements and SQL aren't prepared yet when they get to this point.

I don't have the understanding of this process to do anything like this and my guessing of what I thought happens has ended up dead ended.

The following is code I edited, it is not originated by me. I forget where I got it and who the author is but I am not taking credit for the following code. I did change somethings from the original version and took out everything not relevant to my question / problem.

class my_mysqli_stmt extends mysqli_stmt{

public function __construct($link, $query) {
    parent::__construct($link, $query);
}  

public function fetchAll(){

    echo "FetchAll Method";

    while($this->fetch()){
        # Nothing happens here
        # and don't know what 
        # to insert here if it did  
    }
  }
}


class my_Mysqli extends mysqli {

    public function prepare($query) {
        return new my_Mysqli_stmt($this, $query);
    }
}

The code block above this one with the WHILE statement would be replaced as:

$mysqli->execute();        
$mysqli->store_result();
$mysqli->bind_result($id, $name, $age);     

$mysqli->fetchAll();   <--  This would replace the WHILE loop.

Of course this does not work. Maybe it isn't meant to work this way but it would be nice if it were possible to do this so that the while loop is not necessary and all records are returned with that one call.

Looking at this in code it seems maybe this concept is sloppy thinking on my part but having all the code in one place encapsulated would be great.

Maybe do something like:

$mysqli->fetchAll($id, $name, $age); 

and return bound parameters? I don't know I am confusing myself now.

Ok, relooked at this..

Everytime the Fetch loop happens the parameters are auto updated with relevant data from the row retrieved from the database.

What would happen here or should is that when FetchAll is called it returns an associative array with all the bound parameters and data so that immediately after this you would return the array as it is created within the MYSQLI_STMT class and then returned to FetchAll was initially called from.

BAM! Presto Chango and all records retrieved are in an associative array and ready for use. See, all encapsulated. Problem is.. How? Can't get it to work.

Thanks for reading this Tomb of a question.

Answer 1

I don't think this is possible (I'm running PHP 5.4.4) because there is no way of accessing the bound variables.

Even if there was, it would be hard to tell, implicitly, which bound variables should be assigned each of the values returned by fetch()

I tried overloading the bind_result() function (see below) but ran into trouble as the variables are passed as reference.

public function bind_result() {
    $this->bound_variables = func_get_args();

    // The next line won't work in < 5.3
    call_user_func("parent::bind_result", $this->bound_variables);
}

So I got the following error: Warning: Parameter 1 to mysqli_stmt::bind_result() expected to be a reference, value given...

I also tried adding the fetchAll as part of my_Mysqli instead of the my_mysqli_stmt class, but again, it failed because we can't get the bound variables as references, only as values.

class my_Mysqli extends mysqli {

    public function prepare($query) {
        return new my_Mysqli_stmt($this, $query);
    }

    private $bound_vars;

    public function fetchAll($statement) {

        $args = func_get_args();
        $first = true;
        // Even though we try using 'by reference' here, the arguments themselves
        // are passed in 'by value' by default (because we haven't specified them
        // in the function definition)
        foreach($args as &$a) {
            if ($first) { continue; $first = false; }
            $this->bound_vars[] = $a;
        }

        while($statement->fetch()){
            // This doesn't work, because we haven't got references back to
            // the original variables
            printf ("%s (%s)\n", $this->bound_vars[0], $this->bound_vars[1]);
        }
    }
}
$mysqli = new my_Mysqli();
$stmt = $mysqli->prepare($query);
$stmt->execute();
$stmt->bind_result($id, $age, $name);
$mysqli->fetchAll($stmt, $id, $age, $name);

Hope this helps explain why it doesn't work!

Answer 2

This is quite possible.
The only thing you need is to switch to PDO.

PDO does have everything you need from mysqli and much more: fetchAll out of the box, bindValue(!!!), bind-in-execute, fetchColumn...

If you're doomed to use mysqli, there is another way: you can emulate prepared statements with manually parsed placeholders (just like PDO does) and get even more powerful code. Here is a ready made library for this, which is better both PDO and mysqli, as it supports full range of SQL literals, not only limited part of it.