PHPs mysqli prepare - generate dynamically

Question

My code to generate the SQL statement works fine - however I run into a hiccup when generating the string for $stmt->bind_param(). Code is as follows:

$stmt = $mysqli->stmt_init();
if ($stmt->prepare ($sql)) {
    $bind_types = '"';
    $bind_values = '';

    if ($action == 'insert' || $action == 'update') {
        reset ($array);
        foreach ($array as $key => $value) {
            if (is_string ($value)) { $type = 's'; } else if (is_int ($value)) { $type = 'i'; } else if (is_float ($value)) { $type = 'd'; } else { die ('Cannot determine type for ' . $key . ' => ' . $value . ''); }
            $bind_types .= $type;
            $bind_values .= $value . ', ';
            //$stmt->bind_param ($type, $value);
        }
    }
    
    if ($action == 'update' || $action == 'delete') {
        if (is_string ($id_value)) { $type = 's'; } else if (is_int ($id_value)) { $type = 'i'; } else if (is_float ($id_value)) { $type = 'd'; } else { die ('Cannot determine type for ' . $id_column . ' => ' . $id_value . ''); }
        $bind_types .= $type;
        $bind_values .= $id_value . ', ';
        //$stmt->bind_param ($type, $id_value);
    }
    
    $bind_types .= '"';
    
    $bind_values = substr ($bind_values, 0, -2);
    
    echo $bind_types  . ', ' . $bind_values;
    
    $stmt->bind_param ($bind_types, $bind_values);
    $stmt->execute();
    
}

The formatting of that got messed up. I apologize if its hard to read.

I am getting the following error:

"Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in ... "

Any ideas?

So much `mysql_` and `mysqli` in the last hour... use [PDO](http://us2.php.net/manual/en/book.pdo.php) instead. — Waleed Khan, Jul 18 '12 at 02:32
I was looking into it. However the arguments for using mysqli vs. PDO are good on both sides. I figured mysqli was the way to roll. Maybe not. — Luke Pittman, Jul 18 '12 at 02:33
I wasn't under the impression that there was much of an argument, but I will go view said arguments now. — Waleed Khan, Jul 18 '12 at 02:35
@LukePittman you will find that it usually not worth specifying the data types for the binding call. Using `s` for each variable is generally enough. You might prefer the cleanliness of [this approach](https://stackoverflow.com/a/51036322/2943403) -- you should have no trouble adapting it to uour needs. — mickmackusa, Apr 03 '22 at 09:07

score 3 · Accepted Answer · answered Jul 18 '12 at 02:47

I would highly advise to use PDO as you can do it easily. If you want to do it in mysqli it is more complicated since you can't easily bind them dynamically. To bind them dynamically look at this ugly hack

$bind_values= explode(',', $bind_values);
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($bind_values));
$stmt->execute();

function makeValuesReferenced(&$arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 

}

I am looking at PDO right now and will most likely migrate this test code over to that to test it out. Additionally you actually answered my question, so thanks. — Luke Pittman, Jul 18 '12 at 02:48

score 0 · Answer 2 · answered Jul 18 '12 at 02:36

0

Your call to bind_param is wrong: it should be:

bind_param($types, $value1, $value2, $value3 ...);

where each of those values is an actual variable.

answered Jul 18 '12 at 02:36

Waleed Khan

11,426
6
39
70

I understand that. If you look closer at the code you'll see loops generate $bind_types and $bind_values. Then I am using those in the bind_param call. I suspect that still may be the problem although the output of those two strings is correct. – Luke Pittman Jul 18 '12 at 02:40
`$bind_values` is a string, and `bind_param` does not take a string as the second parameter. – Waleed Khan Jul 18 '12 at 02:41
PDO lets you bind values individually... tempted? – Waleed Khan Jul 18 '12 at 02:42
Haha - yes. I just noticed that. – Luke Pittman Jul 18 '12 at 02:43

PHPs mysqli prepare - generate dynamically

2 Answers2

Linked