使用预准备语句时的预定义语句

Question

Firstly, sorry if my title doesn't quite explain the situation, I had problems thinking of a good one.
I'm trying to take all the input from a form and put them into a database. I'm not 100% sure at this point if this is possible, but I thought I'd post it to make sure. The code is commented, but I get

INSERT INTO items (title, description, category, size) VALUES (?, ?, ?, ?)
'ssss', $fields[0][1], $fields[1][1], $fields[2][1], $fields[3][1]
Warning: Wrong parameter count for mysqli_stmt::bind_param()

I'm guessing this is because I'm passing the variable, and not its value. But even if I did pass its value, whether or not it's good code is questionable to me.

        // $fields[x][0] is title of x, $fields[x][1] is value of x
        $statement = 'INSERT INTO items (';
        // Add all the field names
        for ($i = 0; $i < count($fields) - 1; $i++) {
            $statement .= $fields[$i][0] . ', ';
        }
        $statement = $statement . $fields[count($fields) - 1][0] . ') VALUES (';
        // Add '?' for every field
        for ($i = 0; $i < count($fields) - 1; $i++) {
            $statement .= '?, ';
        }
        $statement = $statement . '?)';
        echo $statement . '<br />';
        // Add all the value types to the statement
        $params = '\'';
        for ($i = 0; $i < count($fields); $i++) {
            if (gettype($fields[$i][1]) == 'integer') {
                $params .= 'i';
            } else {
                $params .= 's';
            }
        }
        $params .= '\', ';
        // Put the values into the statement
        for ($i = 0; $i < count($fields) - 1; $i++) {
            $params .= '$fields[' . $i . '][1], ';
        }
        $i = count($fields) - 1;
        $params .= '$fields[' . $i . '][1]';
        echo $params;
        // Put it all in the database
        $db = mysqlConnect();
        $stmt = $db->stmt_init();
        $stmt->prepare($statement);
        $stmt->bind_param($params);
        $stmt->close();
        $db->close();

Answer 1

From PHP manual for bind_param: (http://php.net/manual/en/mysqli-stmt.bind-param.php)

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

You are calling

$stmt->bind_param($params);

So you should have the types in a separate variable, not in $params and call it like this:

$stmt->bind_param($types,$params);

Keep in mind that this looks a bad idea to me in general. Since the items table has a specific schema, you should probably have a hardcoded query and types variable, and just put the values to params array in the required format.

You'll have cleaner code and won't have to deal with ill-formed fields array.