Solved

convert json array to mysql text

Posted on 2011-09-12
21
938 Views
Last Modified: 2012-05-12
How can I convert encoded to json array to insert into mysql db. If I insert at the moment all I am getting is Array in the column. $box is an array initially so dosen't need to be dealt with because json_encode creates the array. So I need to convert the json_encode to a mysql text format. Thanks
$box = $_POST['box'];



$list = array("activity" => $activity, "mobile" => $mobile, "company" =>
$company, "authorised" => $authorised, "service" => $service, "department" =>
$department, "address" => $address, "boxcount" => $boxcount, "boxnumber" => $box);



$c = json_encode($list);

echo $c;

Open in new window

0
Comment
Question by:peter_coop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
  • +1
21 Comments
 
LVL 40

Expert Comment

by:gurvinder372
ID: 36522171
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36522184
Are you sure you want a JSON string?  Maybe just serialize() will work for you.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:peter_coop
ID: 36522299
@gurvinder
already read those at SO. Couldn't see how they would help with my problem. But I am a noob with arrays that I am probably wrong :-)

@ray
Is my $list not already serialized?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36522337
$list appears to be an array.  A serialized array is a string.
http://php.net/manual/en/function.serialize.php
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36522360
how do you insert the string in mysql?
json_encode return a string but if you may have simple quotes inside it

you may need : http://www.php.net/manual/en/function.addslashes.php
0
 

Author Comment

by:peter_coop
ID: 36522395
@leak
I have posted mysql insert code.
$query = 'INSERT INTO `act` (`service`, `activity`, `department`, `company`,  `address`, `user`, `item`, `destroydate`, `date`, `notes`, `new`)
         VALUES (\''.$service.'\', \''.$activity.'\', \''.$department.'\', \''.$company.'\', \''.$address.'\', \''.$authorised.'\', \''.strtoupper($box).'\', NULL, NOW(), \''.$mobile.'\', \''.$new.'\');';
mysql_query($query) or die(mysql_errno());

Open in new window

0
 

Author Comment

by:peter_coop
ID: 36522408
also, there will never be any \ in the passed data.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36522421
so in the item column you see "array"? right?

if it's the case, it's because you did not send the array the right way from the webpage (client side), so javascript just send you the type of the object, you need to transform your array in a string with javascript for example with :

var box = your_box_array.toString(); // this is your javascript
0
 

Author Comment

by:peter_coop
ID: 36522924
I am using jquery serialize function to read form data then using ajax to post to php page. This is the js that handles the box input. Thanks
for(var i = 0;i < $(this).val();i++) {
        $("#box").append('<div data-role="fieldcontain"><label for="box" class="ui-input-text">Enter box ' + (i + 1) + ' number:</label><input type="text" name="box['+i+']" id="box['+i+']" class="box ui-input-text ui-body-null ui-corner-all ui-shadow-inset ui-body-c" /></div>')
      }

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
ID: 36522944
where's the JS ajax call code?
0
 

Author Comment

by:peter_coop
ID: 36522996
fyi. I have included data from print_r and var_dump.
output data

<pre>Array
(
    [activity] => Box Retrieval
    [mobile] => Submitted from mobile
    [company] => DEMO
    [authorised] => DEMO
    [service] => standard
    [department] => DEMO
    [address] => 28 mile end road   london e1 7yu
    [boxcount] => 2
    [box] => Array
        (
            [0] => ttt
            [1] => fff
        )

)
string(323) "a:9:{s:8:"activity";s:13:"Box Retrieval";s:6:"mobile";s:21:"Submitted from mobile";s:7:"company";s:4:"DEMO";s:10:"authorised";s:4:"DEMO";s:7:"service";s:8:"standard";s:10:"department";s:4:"DEMO";s:7:"address";s:32:"28 mile end road   london e1 7yu";s:8:"boxcount";s:1:"2";s:9:"box";a:2:{i:0;s:3:"ttt";i:1;s:3:"fff";}}"
</pre>

jquery code

$(function() {

 $("#BRV_brtrv").submit(function() {

   var send = $(this).serialize();

    $.ajax({
      type: "POST",
      url: "boxrtrvtemp.php",
      cache: false,
      data: send,
      dataType: "json",
      success: function (data) {
      /*if (data.ErrorService){
      $('#brtv-result').addClass("result_msg").html(data.ErrorService).show(1000).delay(1000).fadeOut(1000);
      }*/

      //location.reload(true);
      $('#brtv-result').addClass("result_msg").html("You have successfully retrieved: "+data.box).show(1000).delay(4000).fadeOut(4000);
      $("#BRV-brtrv-slider").val(0).slider("refresh");
      $("input[type='radio']").attr("checked",false).checkboxradio("refresh");
      var myselect = $("select#BRV-brtrv-department");
      myselect[0].selectedIndex = 0;
      myselect.selectmenu("refresh");
      var myselect = $("select#BRV-brtrv-address");
      myselect[0].selectedIndex = 0;
      myselect.selectmenu("refresh");

     },
      error:function (xhr, ajaxOptions, thrownError, data){
      alert(thrownError);
      alert(xhr.status);
      alert(data);
     }
   });
   return false;
  });
});

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
ID: 36523129
thank you,
we can see clearly than box is an array and need to be serialized : serialize($box)
all other fields are string

so your query :

$query = 'INSERT INTO `act` (`service`, `activity`, `department`, `company`,  `address`, `user`, `item`, `destroydate`, `date`, `notes`, `new`)
         VALUES (\''.$service.'\', \''.$activity.'\', \''.$department.'\', \''.$company.'\', \''.$address.'\', \''.$authorised.'\', \''.strtoupper(serialize($box)).'\', NULL, NOW(), \''.$mobile.'\', \''.$new.'\');';

Open in new window


when reading the database you will need to unserialize
additionaly don't forget some address may have simple quote...
a good article (don't forget to vote): http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html

0
 

Author Comment

by:peter_coop
ID: 36523368
@leak
This is what is being inserted into the db:
A:2:{I:0;S:3:"GGG";I:1;S:3:"HHH";} Thanks
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36523398
this is better than Array no?

As mentionned in my previous post, you will need to unserialize when reading
if you want GGG,HHH use : implode(",", $box)
$query = 'INSERT INTO `act` (`service`, `activity`, `department`, `company`,  `address`, `user`, `item`, `destroydate`, `date`, `notes`, `new`)
         VALUES (\''.$service.'\', \''.$activity.'\', \''.$department.'\', \''.$company.'\', \''.$address.'\', \''.$authorised.'\', \''.strtoupper(implode(",", $box)).'\', NULL, NOW(), \''.$mobile.'\', \''.$new.'\');';

Open in new window

0
 

Author Comment

by:peter_coop
ID: 36523464
Now that as entered the data in the column like so: EEE,WWW
I need to separated the values so that each item is inserted into there own column.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36523486
That's great!!!!!!!!!!! No???

what is the column name of EEE?
and the column name of WWW?
0
 

Author Comment

by:peter_coop
ID: 36523516
Sorry, I didn't think I had to explain the part about insert separate values. Cannot see a reason why values would be included that way. The field is: `item`. thanks
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 36523547
I suppose you want to create a RECORD by ITEM (box)
In this case you need to loop over all item in the box to insert them one after one

foreach($box as $item) {

$query = 'INSERT INTO `act` (`service`, `activity`, `department`, `company`,  `address`, `user`, `item`, `destroydate`, `date`, `notes`, `new`)
         VALUES (\''.$service.'\', \''.$activity.'\', \''.$department.'\', \''.$company.'\', \''.$address.'\', \''.$authorised.'\', \''.strtoupper($item).'\', NULL, NOW(), \''.$mobile.'\', \''.$new.'\');';
mysql_query($query) or die(mysql_errno());

}

Open in new window

0
 

Author Comment

by:peter_coop
ID: 36523599
perfect. Thank you ever so much and I have raised the points to reflect the work.
0
 

Author Closing Comment

by:peter_coop
ID: 36523604
thank you
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question