使用PHP从sql查询中获取特定的JSON数据

Question

I would like to say thank you for reading this question.

And my question is. I have this php code with sql query:

mysql_connect($mysql_server, $mysql_login, $mysql_password);
mysql_select_db($mysql_database);

$req = "SELECT name, elements "
."FROM lwzax_zoo_item "
."WHERE application_id = '2' AND elements LIKE '%".$_REQUEST['term']."%' ";

$query = mysql_query($req);

while($row = mysql_fetch_array($query))
{
    $results[] = array('label' => $row['name'], 'desc' => $row['elements']);
}


$json = json_encode($results);
echo $json;

And output is:

[  
 {  
  "label":"0146T",
  "desc":" {
\t\"cec36dd6-ffde-494d-b25c-8e58bff84e22\": {
\t\t\"0\":        {
\t\t\t\"value\": \"Ccta W\\/Wo Dye\"
\t\t}
\t}
}"
 },
 {  
  "label":"64653",
  "desc":" {
\t\"cec36dd6-ffde-494d-b25c-8e58bff84e22\": {
\t\t\"0\": {
\t\t\t\"value\": \"Chemodenervation Eccrine Glands Oth Area Per Day\"
\t\t}
\t}
}"
 }
]

But I need only label data and value data...so it should look like:

[  
 {  
  "label":"0146T",
  "desc":"Ccta W\\/Wo Dye"
 },
 {  
  "label":"64653",
  "desc":"Chemodenervation Eccrine Glands Oth Area Per Day"
 }
]

Could you please help me? Thank you very much for help

UPDATE: Deleted $b = json_decode($row['desc'], true); as it wasn't used, just a junk from all my attempts to succeed.

Answer 1

You're decoding the JSON and assigning it to $b, but you're not doing anything with that variable. Use:

    $results[] = array('label' => $row['name'],
                       'desc' => $b['cec36dd6-ffde-494d-b25c-8e58bff84e22'][0]['value']);

Also, you need to give a second argument to json_decode, so it will return an associative array rather than an object.

$b = json_decode($row['elements'], true);