Error :
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 ‘”docrefno”:1, “maindatakey”:”x001592″, “maindata”: { ‘ at line 3
{
"docrefno":1,
"maindatakey":"x001592",
"maindata": {
"outdata":["outside1","outside2","outside3","outside4"],
"prmdata":{
"billno":"bl-2023/01",
"billdate":"01/12/2020"
},
"datapart1":{
"customerid":"cust01",
"gststate":"west bengal",
"gstrate":28
},
"datapart2":[
{"datakey":"prod01","tprodcode":"prod01","quantity":10,"rate":20,"amount":200,"discountprcn":10,"discamt":20},
{"datakey":"prod02","tprodcode":"prod02","quantity":20,"rate":30,"amount":600,"discountprcn":10,"discamt":60},
{"datakey":"prod03","tprodcode":"prod03","quantity":30,"rate":40,"amount":1200,"discountprcn":10,"discamt":120},
{"datakey":"prod04","tprodcode":"prod04","quantity":40,"rate":50,"amount":2000,"discountprcn":10,"discamt":200},
{"datakey":"prod05","tprodcode":"prod05","quantity":50,"rate":60,"amount":3000,"discountprcn":10,"discamt":300},
{"datakey":"prod06","tprodcode":"prod06","quantity":60,"rate":70,"amount":4200,"discountprcn":10,"discamt":420},
{"datakey":"prod07","tprodcode":"prod07","quantity":70,"rate":80,"amount":5600,"discountprcn":10,"discamt":560}
],
"datapart3":[
{"datakey":"3prod01","tprodcode3":"3prod01","quantity3":10,"rate3":20,"amount3":200,"discountprcn3":10,"discamt3":20},
{"datakey":"3prod02","tprodcode3":"3prod02","quantity3":20,"rate3":30,"amount3":600,"discountprcn3":10,"discamt3":60},
{"datakey":"3prod03","tprodcode3":"3prod03","quantity3":30,"rate3":40,"amount3":1200,"discountprcn3":10,"discamt3":120},
{"datakey":"3prod04","tprodcode3":"3prod04","quantity3":40,"rate3":50,"amount3":2000,"discountprcn3":10,"discamt3":200},
{"datakey":"3prod05","tprodcode3":"3prod05","quantity3":50,"rate3":60,"amount3":3000,"discountprcn3":10,"discamt3":300},
{"datakey":"3prod06","tprodcode3":"3prod06","quantity3":60,"rate3":70,"amount3":4200,"discountprcn3":10,"discamt3":420},
{"datakey":"3prod07","tprodcode3":"3prod07","quantity3":70,"rate3":80,"amount3":5600,"discountprcn3":10,"discamt3":560}
],
"datapart4":[
{"datakey":"4prod01","tprodcode4":"4prod01","quantity4":10,"rate4":20,"amount4":200,"discountprcn4":10,"discamt4":20},
{"datakey":"4prod02","tprodcode4":"4prod02","quantity4":20,"rate4":30,"amount4":600,"discountprcn4":10,"discamt4":60},
{"datakey":"4prod03","tprodcode4":"4prod03","quantity4":30,"rate4":40,"amount4":1200,"discountprcn4":10,"discamt4":120},
{"datakey":"4prod04","tprodcode4":"4prod04","quantity4":40,"rate4":50,"amount4":2000,"discountprcn4":10,"discamt4":200},
{"datakey":"4prod05","tprodcode4":"4prod05","quantity4":50,"rate4":60,"amount4":3000,"discountprcn4":10,"discamt4":300},
{"datakey":"4prod06","tprodcode4":"4prod06","quantity4":60,"rate4":70,"amount4":4200,"discountprcn4":10,"discamt4":420},
{"datakey":"4prod07","tprodcode4":"4prod07","quantity4":70,"rate4":80,"amount4":5600,"discountprcn4":10,"discamt4":560}
],
"datapart5":[
{"datakey":"5prod01","tprodcode5":"5prod01","quantity5":10,"rate5":20,"amount5":200,"discountprcn5":10,"discamt5":20},
{"datakey":"5prod02","tprodcode5":"5prod02","quantity5":20,"rate5":30,"amount5":600,"discountprcn5":10,"discamt5":60},
{"datakey":"5prod03","tprodcode5":"5prod03","quantity5":30,"rate5":40,"amount5":1200,"discountprcn5":10,"discamt5":120},
{"datakey":"5prod04","tprodcode5":"5prod04","quantity5":40,"rate5":50,"amount5":2000,"discountprcn5":10,"discamt5":200},
{"datakey":"5prod05","tprodcode5":"5prod05","quantity5":50,"rate5":60,"amount5":3000,"discountprcn5":10,"discamt5":300},
{"datakey":"5prod06","tprodcode5":"5prod06","quantity5":60,"rate5":70,"amount5":4200,"discountprcn5":10,"discamt5":420},
{"datakey":"5prod07","tprodcode5":"5prod07","quantity5":70,"rate5":80,"amount5":5600,"discountprcn5":10,"discamt5":560}
],
"datapart6":{
"shipingdetail":"no shiping detail",
"shipingamount":1500.50
},
"datapart7":{
"gstdetail":"gst detail to input here, it is like address field",
"gstamount":2000
}
}
}
===========================
my table structure is below
==========================
TABLE CUSTOMERMASTER(
DOCREFNO INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
MAINDATAKEY VARCHAR(400) NOT NULL UNIQUE,
RECORDSTATUS VARCHAR(10) NOT NULL,
MAINDATA JSON NOT NULL
) ENGINE = INNODB";
I validated JSON online and it is showing valid JSON, in php it is not working
I tried
=======
$addsql = "INSERT INTO customermaster (DOCREFNO, MAINDATAKEY, RECORDSTATUS, MAINDATA) VALUES
((1,'CUST01','ACTIVE',$data1))";
try
{
$conn->query($addsql);
}
catch (Exception $e)
{
echo "Error Save Data: " . $conn->error;
}
Error i am getting
===========
ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”docrefno”:1, “maindatakey”:”x001592″, “maindata”: { ‘ at line 3