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

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, Sep 14 '15 at 17:56

Your Common Sense · Answer 1 · 2015-09-14T11:15:18.053

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.

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? — Saeed Ansari, Sep 14 '15 at 11:15

score 0 · Accepted Answer · answered Sep 14 '15 at 16:12

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 '15 at 16:12

Danijel

12,408
5
38
54

I am liking your idea let me try It out till @your common sense answer my question – Saeed Ansari Sep 14 '15 at 18:01
could you please make it like if I want to select * without any param like `select * from members` – Saeed Ansari Sep 14 '15 at 22:06
@SaeedAnsari, `$bind` is an optional param, just use `$db->prepare( "select * from members" );` – Danijel Sep 15 '15 at 13:02
Thanks I am using it now :) – Saeed Ansari Sep 15 '15 at 20:31
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;` – Saeed Ansari Sep 16 '15 at 18:59
You can use get_result() only if the query yields a result, for INSERT statements you can return insert_id if you like, check if $stmt has results with `return ( $result = $stmt->get_result() ) ? $result : $stmt->insert_id;` – Danijel Sep 16 '15 at 19:23
It has results and my data inserted successfully... The thing Is I am making universal function which will perform both insert and select queries... is there any way I can achieve both? – Saeed Ansari Sep 16 '15 at 20:06
in PDO below mention code works fine `if($this ->_query->execute()){ $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ); $this->_count = $this->_query->rowCount(); }` for both insert and select I want same thing in mysqli – Saeed Ansari Sep 16 '15 at 20:10
I print insert results and got this is it fine? `mysqli_result Object ( [current_field] => 0 [field_count] => 39 [lengths] => [num_rows] => 0 [type] => 0 ) ` – Saeed Ansari Sep 17 '15 at 08:31
this query is failed to bring result `"select * from `geo_ips` where ? between `ip_start` and `ip_end`", array('i', $vpb_long_ip)` – Saeed Ansari Sep 17 '15 at 11:29
This comments and code inside have gone away from the original question, why don't you ask another question about how to return proper data depending on the type of SQL query and I'll gladly write an answer. – Danijel Sep 17 '15 at 14:49

Dynamically bind params in $bind_param(); Mysqli

2 Answers2

Linked

Related