Dynamically bind params in $bind_param(); Mysqli

Question

I have DB class which is dealing all queries will be made to database I have mysqli prepare working fine. bind_param is also working fine but the problem is I want to define variable type dynamically. here is my code

public function query($sql, $params = array()){
        $this->_error = false;
        if($this->_query = $this->_mysqli->prepare($sql)){
            $x = 1;
            if(count($params)){
                foreach($params as $param){
                    $this->_query->bind_param($x, $param);
                    $x++;
                }
            }

IN PDO fist parameter defines position I guess so this function runs fine by setting X = 1 and x++ everytime, but in bind_param first argument defines type I guess as php.net manual says so is there is any way if user pushes integral value I set x = i for string x = s so on and so forth for all 4 types ...

like

if((int)$param->){
    x = i;
}

any Idea guys?

thanks in advance

Answer 1

For types it's easy. Just use s all the way around.

There is a much more complex problem: in fact, you cannot bind in a loop, so, have to use call_user_func()

public function query($sql, $params = array())
{
    if (!$params)
    {
        return $this->_mysqli->query($sql);
    }
    $stmt = $this->_mysqli->prepare($sql);
    $types = str_repeat("s", count($params));

    if (strnatcmp(phpversion(),'5.3') >= 0)
    {
        $bind = array();
        foreach($values as $key => $val)
        {
            $bind[$key] = &$params[$key];
        }
    } else {
        $bind = $values;
    }

    array_unshift($bind, $types);
    call_user_func_array(array($stmt, 'bind_param'), $bind);

    $stmt->execute();
    return $stmt->get_result();
}

Note that you shouldn't assign a statement to a local variable and there is no use for the error variable as well. Exceptions are better in every way.

Looking at the code above you should think twice before turning over PDO, which will take only three lines for such a function:

public function query($sql, $params = array())
{
    $stmt = $this->_pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

If you have no experience with PDO, here is a PDO tutorial I wrote, from which you will learn that it's most simple yet powerful database API, getting you data in dozens different formats, with very little amount of code.

Answer 2

Here is an example that could help ( prepare() function is a class method ).

function prepare( $query, $bind = array() )
{   
    if ( !$stmt = $this->mysqli->prepare( $query ) ) 
        throw new Exception( 'Query failed: ' . $query . PHP_EOL . $this->mysqli->error );  

    // if $bind is not an empty array shift the type element off the beginning and call stmt->bind_param() with variables to bind passed as reference
    if ( $type = array_shift( $bind ) )
        call_user_func_array( 
            array( $stmt, 'bind_param' ), 
            array_merge( array( $type ), array_map( function( &$item ) { return $item; }, $bind ) ) 
        );

    if ( !$stmt->execute() ) 
        throw new Exception( 'Execute failed: ' . PHP_EOL . $stmt->error );

    // choose what to return here ( 'affected_rows', 'insert_id', 'mysqli_result', 'stmt', 'array' ) 

}

Example of usage:

$db->prepare( "SELECT * FROM user WHERE user_name = ? OR user_email = ?", [ 'ss', $user_name, $user_name ] );