Solved

convert json array to mysql text

Posted on 2011-09-12
21
912 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
  • 10
  • 7
  • 2
  • +1
21 Comments
 
LVL 40

Expert Comment

by:gurvinder372
Comment Utility
0
 
LVL 40

Expert Comment

by:gurvinder372
Comment Utility
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Are you sure you want a JSON string?  Maybe just serialize() will work for you.
0
 

Author Comment

by:peter_coop
Comment Utility
@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 108

Expert Comment

by:Ray Paseur
Comment Utility
$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
Comment Utility
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
Comment Utility
@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
Comment Utility
also, there will never be any \ in the passed data.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 82

Expert Comment

by:leakim971
Comment Utility
where's the JS ajax call code?
0
 

Author Comment

by:peter_coop
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
That's great!!!!!!!!!!! No???

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

Author Comment

by:peter_coop
Comment Utility
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
Comment Utility
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
Comment Utility
perfect. Thank you ever so much and I have raised the points to reflect the work.
0
 

Author Closing Comment

by:peter_coop
Comment Utility
thank you
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now