Using array_keys() for insert

Question

Inserting an array into the database using this solution Insert array into MySQL database with PHP produces a MySql error.

My code:

// Create arrays and values
        $columns = "`".implode("`, `",array_keys($value))."`";
        $escaped_values = array_map('mysql_real_escape_string', array_values($value));
        $values  = implode("', '", $escaped_values);
        // Print arrays and values
        echo "<br><h4>Columns:</h4> <br>";
        echo $columns;
        echo "<br><h4>Values:</h4><br>";
        echo $values;
        echo "<br><h4>";
        // Insert or update
        $insert = mysql_query("INSERT INTO stocklink2 ($columns) VALUES ('$values') ON DUPLICATE KEY UPDATE ($columns) = ('$values'); ");
        if($insert === FALSE) { 
            die(mysql_errno($link).mysql_error()); // TODO: better error handling
        }
        echo "</h4>";

And the code output:

Columns:

`RecNo`, `Style`, `Upper`, `Split`, `Stocktype`, `Lineno`, `Upper2`,
 `Upper3`, `Upper4`, `Upper5`, `Bottom`, `Price`, `Comments`, `Comments2`, 
`Stocka`, `Stockb`, `Stockc`, `Stockd`, `Stocke`, `Stockf`, `Stockg`, 
`Stockh`, `Stocki`, `Stockj`, `Stockk`, `Stockl`, `Stockm`, `Stockn`, 
`Stocko`, `Resa`, `Resb`, `Resc`, `Resd`, `Rese`, `Resf`, `Resg`, `Resh`, 
`Resi`, `Resj`, `Resk`, `Resl`, `Resm`, `Resn`, `Reso`, `Ordera`, `Orderb`, 
`Orderc`, `Orderd`, `Ordere`, `Orderf`, `Orderg`, `Orderh`, `Orderi`, 
`Orderj`, `Orderk`, `Orderl`, `Orderm`, `Ordern`, `Ordero`, `Availa`, 
`Availb`, `Availc`, `Availd`, `Availe`, `Availf`, `Availg`, `Availh`, 
`Availi`, `Availj`, `Availk`, `Availl`, `Availm`, `Availn`, `Availo`, 
`Currsales`, `Sixmonth`, `Size25s`, `Size30s`, `Size35s`, `Size40s`, 
`Size45s`, `Size50s`, `Size55s`, `Size60s`, `Size65s`, `Size70s`, `Size75s`, 
`Size80s`, `Size85s`, `Size90s`, `Size95s`, `Size100s`, `Size105s`, 
`Size110s`, `Size115s`, `Size120s`, `Size125s`, `Size130s`, `Size135s`,
`Size140s`, `Size145s`, `Size150s`, `Size20c`, `Size25c`, `Size015c`, 
`Size10c`, `Size30c`, `Size35c`, `Size40c`, `Size45c`, `Size50c`, `Size55c`, 
`Size60c`, `Size65c`, `Size70c`, `Size75c`, `Size80c`, `Size85c`, `Size90c`, 
`Size95c`, `Size100c`, `Size105c`, `Size110c`, `Size115c`, `Size120c`, 
`Size125c`, `Size130c`, `Size135c`, `Size140c`, `Size145c`, `Size150c` 
Values:


2', 'BNO5839 ', 'CCA ', '8', ' ', '0', ' ', ' ', ' ', ' ', 'BO ', '120', ' 
', ' ', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '159', '2', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '-328', '-2', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '-418', '-4', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0 1064You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near 
'(`RecNo`, `Style`, `Upper`, `Split`, `Stocktype`, `Lineno`, `Upper2`, 
`Upper3`, ' at line 1

The column "Upper" is a reserved word, so I have to escape the column names. This worked when I was updating a single field, but does not work with the array of fields.

Any particular reason you're ignoring the big red warning notice at the top of the page here php.net/mysql_query ? — Mike
– Mike, Commented Apr 25, 2016 at 19:24
ON DUPLICATE KEY UPDATE (a, b, c) = ('x','y','z'); Where did you hear that this is valid syntax? I can see no evidence of it in the documentation for INSERT. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Apr 25, 2016 at 19:30
Thanks for the pointer @Mike , I will convert to mysqli asap. — Robin Bantjes
– Robin Bantjes, Commented Apr 25, 2016 at 20:07
@RobinBantjes If Marc B answered your question remember to accept his answer. — Mike
– Mike, Commented Apr 25, 2016 at 20:09

Marc B · Accepted Answer · 2016-04-25 19:30:33Z

1

This is wrong:

ON DUPLICATE KEY UPDATE ($columns) = ('$values'); ");
                          ^^^^^^^^^^^^^^^^^^^^^

You're building

on duplicate key update (field, field, field, etc..) = (value, value value, ...)

You can NOT update/assign values in parallel like that. You have to do individual

on duplicate key update field1=value1, field2=value2, etc...

answered Apr 25, 2016 at 19:30

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robin Bantjes Over a year ago

Thank you. Removing the ON DUPLICATE worked for inserting records. Is there still a way to use array_keys for the correct ON DUPLICATE syntax?

Marc B Over a year ago

not the way you're building them. you'd have to create key=value pairs for every element in your array.

Collectives™ on Stack Overflow

Using array_keys() for insert

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related