insert json_encode values into mysql table

Question

I am working in PHP.I have the following values which is done the json_encode function.When i print the variable $vmndetails i got the below details.

[{
   "id":"1",
   "smsid":"4781366",
   "senderid":"289613638",
   "textcontent":"Good day reply",
   "msgdate":"2014-12-17 13:04:20",
   "charsetval":"UTF-8",
   "userid":"",
   "reprtdate":"2014-12-17 11:04:21"
},   
{
   "id":"7",
   "smsid":"4781467",
   "senderid":"289761363",
   "textcontent":"Good",
   "msgdate":"2014-12-17 13:21:18",
   "charsetval":"UTF-8",
   "userid":"",
   "reprtdate":"2014-12-17 11:21:21"
}]

I have to insert these values into my mysql table.

So i wrote the below function.

mysql->query( "INSERT INTO twowaysms(id,smsid,senderid,textcontent,msgdate,charsetval,userid,reprtdate) VALUES "."(" . implode(",",$vmndetails) . ")");

But i am getting an error that 'Insert value list does not match column list: 1136 Column count doesn't match value count at row 1'.I have seen the same question a lot here but i cannot find a solution.How to solve the issue?Please anyone help me..

make sure your $vmndetails variable holds the values that matchs with the total numbers of columns — jogesh_pi
– jogesh_pi, Commented Dec 26, 2014 at 5:58
implode join array elements not array of object elements. — Ranjith
– Ranjith, Commented Dec 26, 2014 at 6:02

jogesh_pi · Accepted Answer · 2014-12-26 06:04:00Z

1

You have mulit-dimensional array data, so try like this -

// http://ideone.com/xX2HNU
$vmndetails = '[{"id":"1","smsid":"4781366","senderid":"289613638","textcontent":"Good day reply","msgdate":"2014-12-17 13:04:20","charsetval":"UTF-8","userid":"","reprtdate":"2014-12-17 11:04:21"},{"id":"7","smsid":"4781467","senderid":"289761363","textcontent":"Good","msgdate":"2014-12-17 13:21:18","charsetval":"UTF-8","userid":"","reprtdate":"2014-12-17 11:21:21"}]';

$vmndetails = json_decode($vmndetails, true);
$values = "";
foreach($vmndetails as $v) {
    $values .= "(".implode(", ", $v)."), "
}
$values = rtrim($values, ", ");

mysql->query( "INSERT INTO 
    wowaysms(id,smsid,senderid,textcontent,msgdate,charsetval,userid,reprtdate) 
    VALUES {$values} ");

answered Dec 26, 2014 at 6:04

jogesh_pi

9,7745 gold badges40 silver badges65 bronze badges

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

5 Comments

Elizabeth Joshy Over a year ago

Thankyou so much for the reply.Its working.There is a small issue,i have given datatype of some details as varchar in table,so single quotes should be there for insertion of values.When i am echoing the insert query values are not having single quotes.What can we do for that?

Elizabeth Joshy Over a year ago

Yes.I got it correctly.I have edited your code like: $values .= "('".implode("','", $v)."'), "; It works fine.Thank you so much.

jogesh_pi Over a year ago

@ElizabethJoshy you have to accept this answer if it helps you.

Robert Over a year ago

@jogesh_pi I gave you my +1, Mrs Elizabeth has been pretty unkind. Let's suppose we have an html form with sooo maaany inputs fields. We collect them as object input1-valueinput1 up to input30-valueinput30 Then we use javascript JSON.stringify to create a json array-string and we POST it to our php process On the php side we income the POST and we can e.g. json_decode the string to a php array. Naming properly both input (html side) and db fields, do you think is possible to make the insert even more automatic?

Robert Over a year ago

@jogesh_pi formerly avoid setting the 30 fields names in the query.

Collectives™ on Stack Overflow

insert json_encode values into mysql table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related