Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

convert json array to mysql text

Posted on 2011-09-12
21
Medium Priority
?
944 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:Gurvinder Pal Singh
ID: 36522171
0
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 36522178
0
 
LVL 111

Expert Comment

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

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

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 111

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

670 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