MySQL Insert error with null values

Question

So i'm trying to insert data into a MySQL table from an array that contains multiple arrays which hold data for each row of a table using the code below:

if (is_array($tbl_data)){
$sql = "INSERT INTO teshsting (agent, event , data1,data2,data3,data4,data5) values ";  
$arrayValues = array();
foreach ($tbl_data as $row){

    $agent = mysql_real_escape_string($row[0]);
    $event = mysql_real_escape_string($row[1]);
    $data1 = mysql_real_escape_string($row[2]);
    $data2 = mysql_real_escape_string($row[3]);
    $data3 = mysql_real_escape_string($row[4]);
    $data4 = mysql_real_escape_string($row[5]);
    $data5 = mysql_real_escape_string($row[6]);

    $value = "($agent,$event,$data1,$data2,$data3,$data4,$data5)";
    array_push($arrayValues, $value);
}   
$sql .=implode(',', $arrayValues);

Quick check using var_dum($sql) produces the following:

INSERT INTO teshsting (agent, event , data1,data2,data3,data4,data5) values (NONE,QUEUESTART,,,,,)/////

The above Sql is invalid due to the multiple commas here :(NONE,QUEUESTART,,,,,)which are generated by empty fields. How can I insert single quotations inside the query to make it valid ? i.e the correct sql syntax:

INSERT INTO teshsting (agent, event , data1,data2,data3,data4,data5) values (NONE,QUEUESTART,'','','','','')

Marc B · Accepted Answer · 2015-05-05 15:24:41Z

2

Either:

$data1 = "'" . mysql_real_escape_string($foo) . "'";

or

$value = "(...,'$data1',...)";

Just don't try both options, which would give you (...,''$data1'',...) and kill the query with syntax errors.

answered May 5, 2015 at 15:24

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

4 Comments

b0w3rb0w3r Over a year ago

Thanks this sort of fixed it but I ran into another error which i'm not to sure about. Why does values (Local/120@disc-agents/n,ADDMEMBER,'','','','','') cause an sql syntax error?

Marc B Over a year ago

does local/120@disc-agents/n look like a valid field name to you?

b0w3rb0w3r Over a year ago

I am inserting the string local/120@disc-agents/n it is not a Field Name.

Marc B Over a year ago

then put quotes around it too. insert ... values (Foo) is telling the db to use whatever value is in the Foo field, which makes no sense, because there's no existing record to get that value from in the first place. insert ... values ('Foo') is just a string that contains the letters F, o, o.

Collectives™ on Stack Overflow

MySQL Insert error with null values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related