Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

convert json array to mysql text

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
peter_coop
Asked:
peter_coop
  • 10
  • 7
  • 2
  • +1
1 Solution
 
Ray PaseurCommented:
Are you sure you want a JSON string?  Maybe just serialize() will work for you.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
peter_coopAuthor Commented:
@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
 
Ray PaseurCommented:
$list appears to be an array.  A serialized array is a string.
http://php.net/manual/en/function.serialize.php
0
 
leakim971PluritechnicianCommented:
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
 
peter_coopAuthor Commented:
@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
 
peter_coopAuthor Commented:
also, there will never be any \ in the passed data.
0
 
leakim971PluritechnicianCommented:
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
 
peter_coopAuthor Commented:
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
 
leakim971PluritechnicianCommented:
where's the JS ajax call code?
0
 
peter_coopAuthor Commented:
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
 
leakim971PluritechnicianCommented:
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
 
peter_coopAuthor Commented:
@leak
This is what is being inserted into the db:
A:2:{I:0;S:3:"GGG";I:1;S:3:"HHH";} Thanks
0
 
leakim971PluritechnicianCommented:
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
 
peter_coopAuthor Commented:
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
 
leakim971PluritechnicianCommented:
That's great!!!!!!!!!!! No???

what is the column name of EEE?
and the column name of WWW?
0
 
peter_coopAuthor Commented:
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
 
leakim971PluritechnicianCommented:
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
 
peter_coopAuthor Commented:
perfect. Thank you ever so much and I have raised the points to reflect the work.
0
 
peter_coopAuthor Commented:
thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now