PHP: Pulling JSON data out of database and displaying it

Question

I'm using a php library from https://sourceforge.net/p/wowarmoryapi/home/Home/. It pulls json data from battle.net and stores it in my database for cache reasons.

code to get json from db (I'm not sure if it's still considered json at this point):

$data = $con->query('SELECT Data FROM wa_guilds');

I use the following code to see the data:

foreach($data as $row) {        
    echo "<span style='color:#ff0099'>";
    var_dump($row);
    echo "</span>"; }

which looks like this minus the errors, that's from another code: pink text is the dump

I've tried various methods, mostly from this site to show my data in a way that's easy to read but I always get error.

This is definitely an object. if (is_object($data)) { echo "yay!"; } <--this works
Once I use $decodedjson = json_decode($data); it's no longer an object and I can't seem to print the results to see what it looks like. var_dump($decodedjson) returns NULL

Finally, when I use the following code:

foreach ($data as $da){     
    echo $da['Data']['character']['name'];  }

returns Warning: Illegal string offset 'character'

and:

foreach ($data as $da){     
    echo $da['character']['name'];  }

returns Notice: Undefined index: character

I don't understand what I'd doing wrong, or right. Do I need to somehow turn $data into a string?

NEW CODE

$sth = $con->query('SELECT Data FROM wa_guilds');
$sth->execute();    
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach($data as $row) {
    foreach($row as $r) {
        $myData = json_decode($r, true);
        echo "<span style='color:#ff0099'>";
        var_dump($myData['Data']); 
        echo "</span>"; }  }

NEW ERROR

NULL NULL

What gives a var_dump($data); right after the $data = $con->query() line? I'm guessing that you are trying to loop on a query reference, instead of the query results. — Maxime Pacary
– Maxime Pacary, Commented May 3, 2013 at 14:52
@FrostyZ that's all the pink text in the photo. It's so I can read and make sure that someone was actually in the database. Oh yeah, it's in a loop because someone suggested that, I just never changed it. Oh, when I take it out of the loop I get this back: object(PDOStatement)#2 (1) { ["queryString"]=> string(26) "SELECT Data FROM wa_guilds" } — Brandi Evans
– Brandi Evans, Commented May 3, 2013 at 15:01

Rolando Isidoro · Accepted Answer · 2013-05-03 16:45:28Z

1

From the warning I'm guessing you're using PDO. If $con is your PDO instance representing a connection to a database, try the following:

$sth = $con->prepare('SELECT Data FROM wa_guilds');
$sth->execute();

$data = $sth->fetchAll(PDO::FETCH_ASSOC);

foreach($data as $row) {
    $myData = json_decode($row['Data'], true);
    echo "<span style='color:#ff0099'>";
    // $myData should now be a PHP array which you can access easily
    print_r($myData);
    echo "</span>";
}

edited May 3, 2013 at 16:45

answered May 3, 2013 at 15:21

Rolando Isidoro

5,1242 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Brandi Evans Over a year ago

Cool, made those changes. $roster = json_decode($data, true); print $roster; returns Warning: json_decode() expects parameter 1 to be string, array given and if i change it to $roster = json_decode($data['Data'], true); it returns Notice: Undefined index: Data But when I just var_dump as you mentioned above it takes away everything before string(100000).

Rolando Isidoro Over a year ago

Can you put on pastebin the result of one print_r($row); instruction in a line between your 2 foreach?

Brandi Evans Over a year ago

pastebin.com/jLMaJLS3 this is from foreach($data as $row) { echo "<span style='color:#ff0099'>"; var_dump($row['Data']); echo "</span>"; }

Brandi Evans Over a year ago

print_r($row); changes the beginning to look like this: Array ( [Data] => {"lastModified":1367433943000,"name":"Teh

Rolando Isidoro Over a year ago

Check the updated answer, it should print a PHP array now which you can access easily.

|

MISJHA · Accepted Answer · 2013-05-03 16:04:27Z

0

You will need to convert the json string first, I'm not sure how many rows you are expecting from the DB but if it's only one you don't need the loop:

$data = $sth->fetchAll(PDO::FETCH_ASSOC);

$decoded = json_decode($data['Data'], true);

echo "<span style='color:#ff0099'>";
var_dump($decoded);
echo "</span>";

if you need a loop it should work like this:

foreach($data as $d)
{
    $decoded = json_decode($d['Data'], true);

    echo "<span style='color:#ff0099'>";
    var_dump($decoded);
    echo "</span>";
}

edited May 3, 2013 at 16:04

answered May 3, 2013 at 15:29

MISJHA

1,0085 silver badges13 bronze badges

5 Comments

Brandi Evans Over a year ago

that gives me this error: Warning: json_decode() expects parameter 1 to be string, array given Warning: Invalid argument supplied for foreach()

$data = $sth->fetchAll(PDO::FETCH_ASSOC);	 	$decoded = json_decode($data, true); 	 	foreach($decoded as $row) {		 		echo "<span style='color:#ff0099'>"; 		var_dump($row['Data']); 		echo "</span>";  		}

Brandi Evans Over a year ago

My 1st bullet up top says this is coming out as an object and not a string.

MISJHA Over a year ago

try my edited version :) I think $data is in mumeric index $data[0]...$data[n]

Brandi Evans Over a year ago

Nuts! Notice: Undefined index: Data and NULL for the first part. The loop returns NULL NULL

MISJHA Over a year ago

:S $d['Data'] has to be an array with a string(100000) and that string is a JSON object... I'm lost now! lol

Collectives™ on Stack Overflow

PHP: Pulling JSON data out of database and displaying it

NEW CODE

NEW ERROR

2 Answers 2

8 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

NEW CODE

NEW ERROR

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related