Insert Array field into mysql in php

Question

When i insert this type of array values directly into Mysql database, I got error like this

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':23:09Z, 36840bd430637, Success, 85.0, 11457922, 10.02, USD, X, M, 59106737WV831' at line 1

and myquery is

 INSERT INTO `transaction`(TIMESTAMP, CORRELATIONID, ACK, VERSION, BUILD, AMT, CURRENCYCODE, AVSCODE, CVV2MATCH, TRANSACTIONID) VALUES (2014-06-26T02:23:09Z, 36840bd430637, Success, 85.0, 11457922, 10.02, USD, X, M, 59106737WV831451U)

Mycode is

$columns = implode(", ",array_keys($result_array));
$escaped_values = array_map('mysql_real_escape_string', array_values($result_array));
$values  = implode(", ", $escaped_values);
echo $sql = "INSERT INTO `transaction`($columns) VALUES ($values)";
$res =mysql_query($sql);

what are the changes can i do?

First, get rid of the echo in echo $sql... then use quotes around your values. That should get you started ;-) — Funk Forty Niner
– Funk Forty Niner, Commented Jun 26, 2014 at 2:37

Ja͢ck · Accepted Answer · 2014-06-26 05:18:35Z

1

First of all, you should escape column names (not always required):

$cols = join(',', array_map(function($name) {
    return '`' . str_replace('`', '``', $name) . '`';
}, array_keys($result_array));

Then, keeping in mind that mysql_real_escape_string() doesn't add quoted enclosures:

$vals = join(',', array_map(function($value) {
    return "'" . mysql_real_escape_string($value) . "'";
}, $result_array);

$sql = "INSERT INTO `transaction` ($cols) VALUES ($vals)";

Lastly, using mysql_ functions is deprecated and you should move onto using either PDO or mysqli.

answered Jun 26, 2014 at 5:18

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

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

Comments

A l w a y s S u n n y · Accepted Answer · 2014-06-26 07:03:32Z

0

What is meaning of echo $sql here? try this way & you can also escape your string firstly.

foreach($singleRow as $key=>$data) //for multiple rows
{
 if(isset($data)){
   $dataArray[$key] = is_string($data) ? mysql_real_escape_string($data) : $data;
   }
}

$tableName='transaction';
$keys = implode(',',array_keys($dataArray));
$data_values = ("'".implode("','",array_values($dataArray))."'");
$insertSql = "INSERT INTO ".$tableName." ($keys) VALUES ($data_values)";
$res =mysql_query($insertSql );

use mysqli_* and PDO anyway

edited Jun 26, 2014 at 7:03

answered Jun 26, 2014 at 5:05

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

1 Comment

Ja͢ck Over a year ago

Why did you throw away the mysql_real_escape_string()?

Collectives™ on Stack Overflow

Insert Array field into mysql in php

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related