Dynamically bind params in bind_param()

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;
}

In PDO I accomplish it by saying at start x = 1 means 1st place holder. at the end I said x++ means next time x = 2 so send time it will bind to 2nd param. But I agree with you about the mysqli so can I use your defined method below? it is complete alternate of my question? — Saeed Ansari
– Saeed Ansari, Commented Sep 14, 2015 at 17:56

Your Common Sense · Accepted Answer · 2015-09-14 11:15:18Z

2

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.

edited Sep 14, 2015 at 11:15

answered Sep 14, 2015 at 11:01

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saeed Ansari Over a year ago

if I use s for integral as well?

Saeed Ansari Over a year ago

I don't have command in PDO this is the reason I am choosing mysqli because I understand it more than PDO what you suggest?

Danijel · Accepted Answer · 2015-09-14 16:12:34Z

0

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 ] );

answered Sep 14, 2015 at 16:12

Danijel

12.8k5 gold badges43 silver badges57 bronze badges

11 Comments

Saeed Ansari Over a year ago

I am liking your idea let me try It out till @your common sense answer my question

Saeed Ansari Over a year ago

could you please make it like if I want to select * without any param like select * from members

Danijel Over a year ago

@SaeedAnsari, $bind is an optional param, just use $db->prepare( "select * from members" );

Saeed Ansari Over a year ago

Thanks I am using it now :)

Saeed Ansari Over a year ago

when I select data using it its working fine but if I insert data using this query i get Call to a member function fetch_assoc() on boolean I am using this three lines of code if stmt is successfully executed $result = $stmt->get_result(); return $result->fetch_assoc(); return $result->num_rows;

|

Collectives™ on Stack Overflow

Dynamically bind params in bind_param()

2 Answers 2

2 Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related