0

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?

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Prinsig
  • 245
  • 3
  • 9
  • 2
    [This answer](http://stackoverflow.com/questions/16120822/mysqli-bind-param-expected-to-be-a-reference-value-given) will probably help you. You need to make an array of references instead of passing your existing `array_merge()` directly... – Michael Berkowski Jul 30 '14 at 19:32
  • 2
    If you're not completely married to using MySQLi you could always jump ship to PDO where you can choose to bind by reference, or by value. Just sayin... – Sammitch Jul 30 '14 at 19:36
  • Yep - things like this are far easier to accomplish with PDO. – Michael Berkowski Jul 30 '14 at 19:38
  • Looking at that other post, I'm still not quite sure what you mean. Do I need to construct the array differently? EDIT: Not completely married to MySQLi - but I'm a bit limited about what I can modify about this project – Prinsig Jul 30 '14 at 19:41
  • 1
    you first loop to generate the field names could be simplified down to just `implode(',', $fields)`, basically. – Marc B Jul 30 '14 at 19:47

1 Answers1

2

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

Robert Mauro
  • 566
  • 8
  • 17