CODEWITHSUNDEEP
phpmysqljson
velvetmonster
velvetmonster

Reputation: 87

Parse json field stored in mysql table as php variable

Trying to pull variables out of a legacy MySQL db (I didn't build it) to create a dynamic php page. On the mysql table, the uploadedImgInfo field is stored as what looks like json.

A stripped-down version of my php page looks like this: 

<? if (isset($_GET['id'])){
    include "connectionFile.php";
$id = preg_replace('#[^0-9]#i','',$_GET['id']);
$sql=mysql_query("SELECT * FROM survey_584582 WHERE id='$id' LIMIT 1");
$fieldsiteCount=mysql_num_rows($sql);
if($fieldsiteCount>0){ 
    while($row=mysql_fetch_array($sql)){
        $name=$row["fieldSiteName"];
        $lat=$row["lat"];
        $lon=$row["lon"];
        $img = $row['uploadedImgInfo'];

        echo $name; 

    }
} else {
    echo "That field site does not exist";
    exit();
}
} else{
echo "error";
exit();
}
?>

When I 

echo $img;

after 

echo $name;

I get

Fieldsite1 

[{"title":"titleText","comment":"commentText","size":"1111","name":"imgName.jpg","filename":"randomNumbers","ext":"jpg" }]

When I use 

echo $name; 
$phpArray = json_decode($img, true);
foreach ($phpArray as $key => $value) { 
    echo "<h2>$key</h2>";
    foreach ($value as $k => $v) { 
        echo "$k | $v <br />"; 
    }
}

I get

Fieldsite1

0

title | titleText

comment | commentText

size | 1111

name | imgName.jpg 

filename | randomNumbers

ext | jpg

How can I extract the title, comment and name to php variables out of the uploadedImgInfo mySQL field? Updating my table is fine.

Calling the variables directly via php would be better, though.

Upvotes: 2

Views: 693

Answers (2)

Ohgodwhy
Ohgodwhy

Reputation: 50787

You actually don't need to iterate the array, you can json_decode it, grab the first array, and then use key's from there.

$phpArray = json_decode($img, true);

echo $phpArray[0]['filename'].'.'.$phpArray[0]['ext'];

Further, in php 5.4 + you can actually use $phpArray = json_decode($img, true)[0]; to grab the first element in the stack so you don't need to call [0] on each variable.

Upvotes: 1

Satya
Satya

Reputation: 8881

how about this

<?php
  $a='';
$b='';
$c='';
foreach ($phpArray as $key => $value) { 
       foreach ($value as $k => $v) { 
        if($k=='title')
              $a=$v;
        if($k=='comment')
              $b=$v;
        if($k=='name')
              $c=$v;   
    }
}

echo $a.'-'.$b.'-'.$c;
?>

Upvotes: 1

Related Questions