user3295583 user3295583 - 7 months ago 39
SQL Question

Data Insertion error while trying to insert a json array?

I am trying to send a json array to mysql data base using google's volley library.
The following is the type of data I am sending:-

[{"vatno":"","area":"Testing bay","status":"0","cstno":"","dbname":"webappdb","name":"ANUP BORDE","emp_role":"Manager","password":"1223","key":"","city":"Pune","shop_name":"Test Shop","mobile":"8600931386"}]


I get a data insertion error something like :-


Data insertion errorYou have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'key,dbname,status,role)VALUES( '', 'ANUP BORDE',
'8600931386', '1223', 'Test Sho' at line 1


I am trying to run this php script.

<?php

include "config.php";
$dbname ="goldmine";
$con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$dbname);
if(!$con)
{
echo "Connection Error".mysqli_connect_error();
}
else{
//echo "";
}

$arrAssoc = json_decode(stripslashes($_POST['jsonarray']), true);
$values="";
foreach($arrAssoc as $aa){
$values.="(";
$values.=" '".$aa['']."',";
$values.=" '".$aa['name']."',";
$values.=" '".$aa['mobile']."',";
$values.=" '".$aa['password']."',";
$values.=" '".$aa['shop_name']."',";
$values.=" '".$aa['area']."',";
$values.=" '".$aa['city']."',";
$values.=" '".$aa['vatno']."',";
$values.=" '".$aa['cstno']."',";
$values.=" '".$aa['key']."',";
$values.=" '".$aa['dbname']."',";
$values.=" '".$aa['status']."',";
$values.=" '".$aa['role']."'";
$values.="),";
}
$values=rtrim($values,',');
//Insert into goldmine_users (name,mobile,password,role)Values('Anup','8600931386','123','admin');

$query="INSERT INTO goldmine_users(name,mobile,password,shop_name,area,city,vatno,cstno,key,dbname,status,role)VALUES".$values;

if(mysqli_query($con,$query))
{
echo "Data inserted";
}else
{
echo "Data insertion error".mysqli_error($con);
}


?>


And this is my volley code to send the data to mysql database.

public void registerUser()
{
final String jsonArray = rolejson.toString().trim();
Log.d("JSON RESPONSE",jsonArray);
StringRequest stringReq = new StringRequest(Request.Method.POST, REGISTERUSER, new Response.Listener<String>() {
@Override
public void onResponse(String response) {

Log.d("USER INFO RESPONSE",response);
}
}, new Response.ErrorListener() {
@Override
public void onErrorResponse(VolleyError error) {

}
}){
@Override
protected Map<String, String> getParams() throws AuthFailureError {
Log.d("LOG_USERINFO", jsonArray);
Map<String, String> params = new HashMap<>();
params.put(KEY_JSONARRAY, jsonArray);
return params;
}
};

RequestQueue rq = Volley.newRequestQueue(this);
rq.add(stringReq);
}


This is how my database looks like:-
enter image description here

Any help or suggestion is welcome.I think I am doing a silly mistake somewhere it would be great if someone would guide me to the correct solution.

Answer
 $query="INSERT INTO goldmine_users(name,mobile,password,shop_name,area,city,vatno,cstno,key,dbname,status,role)VALUES".$values;

key is a reserved keyword and should therefor be escaped in backticks (or something similar)

 $query="INSERT INTO goldmine_users(`name`,`mobile`,`password`,`shop_name`,`area`,`city`,`vatno`,`cstno`,`key`,`dbname`,`status`,`role`)VALUES".$values;
Comments