CODEWITHSUNDEEP
phpjson
Brandi Evans
Brandi Evans

Reputation: 81

PHP: Pulling JSON data out of database and displaying it

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.

  1. This is definitely an object. if (is_object($data)) { echo "yay!"; } <--this works
  2. 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

Upvotes: 0

Views: 702

Answers (2)

Rolando Isidoro
Rolando Isidoro

Reputation: 5114

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>";
}

Upvotes: 1

MISJHA
MISJHA

Reputation: 1008

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>";
}

Upvotes: 0

Related Questions