JSON save in Database and load with JQuery

Question

I create a huge JSON-Object and save it in my database. But when I load the "string" and echo it in PHP, I can't access the JSON Object in JQuery. Do I have to consider something if I want to save my JSON Object in a MySQL Database (when I just create the Array and then echo it with "echo json_encode($arr);" it works fine, but I need to save the Object for caching).

{"247":{"0":"This is a question","1":"","2":"247","3":"0","answers":[["Answer1","960","1"],["Answer 2","962","0"],["Answer 3","961","0"],["Answer 4","963","0"]]},{"248":{"0":"This is a question","1":"","2":"247","3":"0","answers":[["Answer1","960","1"],["Answer 2","962","0"],["Answer 3","961","0"],["Answer 4","963","0"]]}}

just an excerpt

If I just echo this JSON-Object, everything works fine, but if I load the same string from the database and echo it, it doesn't work.

Update 1: forget to tell that I'm using a TEXT-Field with UTF8_general_ci collation

Update 2: Maybe a little bit more code:

function start() {
    $(".start").click(function () {

        $.post("load_script.php", { }, function(data){
            alert(data[247][0]);
        }, "json");

        return false;
    });
}

this loads the script and should alert "This is a question"

<?php
require_once('connect.php');

$ergebnis = mysql_query("SELECT text FROM cache_table ORDER BY RAND() LIMIT 1");
while($row = mysql_fetch_object($ergebnis)) {
    $output = $row->text;
}

echo $output;

?>

this is the script, where I load the database entry with the JSON-Object.

Update 3: I think I solved the problem. Some break sneaked into my JSON-Object so I do this, before the output:

$output = str_replace("\n", "", $output);
$output = str_replace("\r", "", $output);
$output = str_replace("\r\n", "", $output);

Can you use firebug to check that the response from the server is actually exactly what you are expecting? — Tom Haigh
– Tom Haigh, Commented Jun 10, 2009 at 12:02
Ah good hint (I really have to use Firebug more often) and I think I have now find out what the problem is. Some breaks sneak into my Object, if I do this, before the echo, it works fine: $output = str_replace("\n", "", $output); $output = str_replace("\r", "", $output); $output = str_replace("\r\n", "", $output); echo $output; — Christian Strang
– Christian Strang, Commented Jun 10, 2009 at 12:10
Just a tip: $output = str_replace(array("\n","\r","\r\n"), "", $output) — Darren Newton
– Darren Newton, Commented Jun 10, 2009 at 12:20

Shalom Craimer · Accepted Answer · 2009-06-10 12:15:53Z

I'd suggest looking at what your javascript is seeing. Instead of asking jQuery to interpret the json for you, have a look at the raw data:

function start() {
    $(".start").click(function () {

        $.post("load_script.php", { }, function(data){
                alert(data);
        }, "text");

        return false;
    });
}

For example, if part of the string gets oddly encoded because of the UTF-8, this might cause it to appear.

Once you've done that, if you still can't spot the problem, try this code:

var data1, data2;
function start() {
    $(".start").click(function () {

        $.post("load_script.php", {src: "db" }, function(data){
                data1 = data;
        }, "text");

        $.post("load_script.php", {src: "echo" }, function(data){
                data2 = data;
        }, "text");

        if (data1 == data2) {
           alert("data1 == data2");
        }
        else {
           var len = data1.length < data2.length ? data1.length : data2.length;
           for(i=0; i<len; ++i) {
              if (data1.charAt(i) != data2.charAt(i)) {
                 alert("data1 first differs from data2 at character index " + i);
                 break;
              }
           }
        }

        return false;
    });
}

And then change the PHP code to either return the data from the database or simply echo it, depending on the post parameters:

<?php
   if ($_POST['src'] == 'db')) {
      require_once('connect.php');

      $ergebnis = mysql_query("SELECT text FROM cache_table ORDER BY RAND() LIMIT 1");
      while($row = mysql_fetch_object($ergebnis)) {
        $output = $row->text;
      }
   }
   else {
      $output = '{"247":{"0":"This is a question","1":"","2":"247","3":"0","answers":[["Answer1","960","1"],["Answer 2","962","0"],["Answer 3","961","0"],["Answer 4","963","0"]]},{"248":{"0":"This is a question","1":"","2":"247","3":"0","answers":[["Answer1","960","1"],["Answer 2","962","0"],["Answer 3","961","0"],["Answer 4","963","0"]]}}';
   }

echo $output;
?>

Hope that helps!

Thanks for your comprehensive answer! I just found the problem (descriped in my question under "Update 3". But I will use your way if I have problems with this stuff in the future.

donohoe · Accepted Answer · 2010-01-12 15:03:56Z

I got this to work in a slightly different manner. I've tried to illustrate how this was done.

In Plain English:

use urldecode()

In Commented Code Fragments

$json    = $this->getContent($url);  // CURL function to get JSON from service
$result  = json_decode($json, true); // $result is now an associative array

...

$insert  = "INSERT INTO mytable (url, data) ";
$insert .= "VALUES('" . $url . "', '" . urlencode(json_encode($result)) . "') ";
$insert .= "ON DUPLICATE KEY UPDATE url=url";

...

/*
** Figure out when you want to check cache, and then it goes something like this
*/

$sqlSelect = "SELECT * FROM mytable WHERE url='" . $url . "' LIMIT 0,1";

$result = mysql_query($sqlSelect) or die(mysql_error());
$num    = mysql_numrows($result);

if ($num>0) {
    $row   = mysql_fetch_assoc($result);
    $cache = json_decode(urldecode($row['data']), true);
}

Hope this is helpful

Jet · Accepted Answer · 2009-06-10 11:40:59Z

1

Maybe you use varchar field and your string just doesn't fit in 255 chars?

answered Jun 10, 2009 at 11:40

Jet

1,1716 silver badges8 bronze badges

1 Comment

Christian Strang Over a year ago

No sorry, forget to tell that I'm using a TEXT-Field with UTF8_general_ci collation

Collectives™ on Stack Overflow

JSON save in Database and load with JQuery

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related