Laravael4 db raw query using ON DUPLICATE KEY UPDATE drops error

Question

I'm really fighting with a laravel ON DUPLICATE KEY UPDATE query I can't get it to work, so the query basically looks like

foreach ($queries as $query) {
                $update_time = array('update_time' => date('Y-m-d H:i:s'));
                $query = array_merge($update_time, $query);
                $keysString = implode(", ", array_keys($query));                
                $indexes = "";
                $values  = "";
                $updates = "";
                foreach ($query as $i=>$v){
                    $values  .= ':'.$v.',';
                    $updates  .= $i.'="'.$v.'",';
                }
                //$holder = rtrim(str_repeat('?,', count($query)),',');
                $updates = rtrim($updates,',');

    DB::statement("INSERT INTO products ({$keysString}) VALUES ({rtrim($values,',')}) ON DUPLICATE KEY UPDATE {rtrim($updates,',')}")

}

but I get

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

How do I make a prepared statement in laravel4 for raw queries?

Answer 1

By default Laravel binds its data using ? and you are binding your data with :foo, meaning there is a mixture of the two approaches and PDO is getting sad about it.

PDO: Invalid parameter number: mixed named and positional parameters

Something like this should get you going in the right direction:

foreach ($queries as $query) {

    // Add the update time without merging stuff
    $query['update_time'] = date('Y-m-d H:i:s');

    // How many bits of data do we have
    $bindingCount = count($query);

    // Same as before, just get the keys
    $keyString = implode(", ", array_keys($query));

    // Start off a bindings array with just the values
    $bindings = array_values($query);

    $updates = [];

    foreach ($query as $field => $value){
        $updates[] = "{$field} = ?";
        $bindings[] = $value;
    }

    $valueString = implode(',', array_fill(0, $bindingCount, '?'));

    $updateString = implode(',', $updates);

DB::statement("INSERT INTO products ({$keyString}) VALUES ({$valueString}) ON DUPLICATE KEY UPDATE {$updateString}", $bindings);

}