Solved

convert json array to mysql text

Posted on 2011-09-12
21
934 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 40

Expert Comment

by:gurvinder372
ID: 36522178
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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