CODEWITHSUNDEEP
phpmysqliprepared-statement
Prinsig
Prinsig

Reputation: 245

MySQLi Prepared Statements - Passing an Array

I'm having some real difficulties with my code here. I'm trying to pass an array of data to a function and have it dynamically build a INSERT statement using prepared statements.

So I have this:

    public function create($data) {
        global $mysqli;
        foreach ($data as $field => $value) {
            $fields[] = $field;
            $values[] = $value;
        }
        $query = "INSERT INTO " . $this->class_name . " (";
        for ($i = 0; $i < count($fields); $i++) {
            if ($i == (count($fields) - 1)) {
                $query .= "" . $fields[$i] . "";
            } else {
                $query .= "" . $fields[$i] . ", ";
            }
        }
        $query .= ") VALUES (";
        $params = array();
        for ($i = 0; $i < count($values); $i++) {
            if (is_int($values[$i])) {
                $params[] = "i";
            } else {
                $params[]= "s";
            }
            if ($i == (count($values) - 1)) {
                $query .= "?";
            } else {
                $query .= "?, ";
            }
        }
        $query .= ")";
        if ($stmt = $mysqli->prepare($query)) {
            call_user_func_array(array($stmt, "bind_param"), array_merge($params, $values));
        } else {
            die("COULD NOT CONNECT create()");
        }
        //echo $query;
    }

The issues is I keep getting the following error:

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in E:\xampp2\htdocs\school2\application\models\CRUDAObject.php on line 44

I'm pretty new to prepared statements, but I can't work out which format/layout the array needs to be when I pass it.

Can anyone help?

Upvotes: 0

Views: 2742

Answers (1)

Robert Mauro
Robert Mauro

Reputation: 598

As of PHP 5.6 this has become ridiculously simple, without the need for call_user_func_array. The trick is to use the splat ("unpack") operator (...) for your value list. Scroll down for "The Short Version" or feel free to read through to see how I'm handling the various data elements involved in the SQL transaction.

I do three things, using three functions I wrote.

  1. I create a key=>value array where the key is an exact match for the MySQL column name.
  2. I create a string containing the data types (eg: 'ssssdssbn').
  3. I have a function that creates my dynamic references for the insert statement

After that, it's pretty simple.

So, first, the ground work:

// This would be passed to a functionized version of this:
    // $MySQLInsertArray is an array of key=>value where key is db column name
    // $MySQLDataTypes is a list of data types used to bind, eg: 'sssdsdnb'

// This creates the reference pointers for the insert statement (eg: '?,?,?,?')
    $MySQLQs=str_repeat("?,",strlen($MySQLDataTypes)-1)."?";



// Make a *STRING* of the column names
    $ColumnList=implode(",",array_keys($MySQLInsertArray));

// Make an *ARRAY WITH NO KEYS* of the values
    $ValueList=array_values($MySQLInsertArray);

// Do all the fun SQL stuff
    $stmt = $conn->prepare("INSERT INTO ".$TBName." (".$ColumnList.") VALUES (".$MySQLQs.")");

// USE THE UNPACK OPERATOR (...)
    $stmt->bind_param($MySQLDataTypes,...$ValueList);
    $stmt->execute();

The short version is:

  • Build a string of the names of the columns (comma separated).
  • Build an array of the values (no key names) - it is "$ValueList" in the statement below.
  • Use the unpack operator in your bind_param statement to unpack the values in $ValueList. It's literally that easy now.

So, that means, the important part is this and those magic three periods:

$stmt->bind_param($MySQLDataTypes,...$ValueList);

Upvotes: 2

Related Questions