Data Insertion error while trying to insert a json array?

Question

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:-

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.

"key" is a reserved keyword for most database implementations. Did you make sure to escape it in back ticks or something similar? — user6073886
– user6073886, Commented Apr 25, 2016 at 11:07

score 2 · Accepted Answer · 2016-04-25 12:17:37Z

2

 $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;

edited Apr 25, 2016 at 12:17

answered Apr 25, 2016 at 11:13

user6073886

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

Comments

Daan · Accepted Answer · 2016-04-25 10:55:16Z

1

Your amount of columns and the data you want to insert doesn't match. You're trying to insert data in 12 columns however you deliver 13 columns of data:

            $values.="(";
            $values.=" '".$aa['']."',"; //Remove this line
            $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.="),";

//12 columns

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

answered Apr 25, 2016 at 10:55

Daan

12.3k6 gold badges36 silver badges51 bronze badges

4 Comments

user3295583 Over a year ago

I did remove that line it doesn't work it gives me the same error.

Daan Over a year ago

Can you do an echo $values; after the foreach, and post the result in your question?

Rohit Arya Over a year ago

@user3295583 deleted my answer as Daan has suggested same thing.

user3295583 Over a year ago

Rohit I cant delete your answer.You will have to delete your answer.Thank you anyways.

Collectives™ on Stack Overflow

Data Insertion error while trying to insert a json array?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related