How to convert Strings from MySql to JSON array

Question

I am trying to convert my data received from MySql into JSON, in following format -

[
    {
        "question": "This is a question",
        "options": [
            "Option which may contain double or single quoted text",
            "Option 2",
            "Option 3",
            "Option 4"
        ]
    }
]

My relevant PHP code, so far is -

<?php
$result = mysql_query("select * from test_table limit 5", $db);  
$json_response = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$option_01 = $row['Option_1'];
$option_02 = $row['Option_2'];
$option_03 = $row['Option_3'];
$option_04 = $row['Option_4'];

$row_array['question'] = $row['Question'];

// I am unable to format Options ($option_01,$option_02,$option_03,$option_04) into desired Array as mentioned in starting
$row_array['options'] = "";
array_push($json_response,$row_array);
}
echo json_encode($json_response);
?>

But I am stuck on how to make array of option variables, received from MySql database?

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

You need to make $row_array['options'] an array

$row_array['options'] = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $row_array['options'][] = $row['Option_1'];
   $row_array['options'][] = $row['Option_2'];
   $row_array['options'][] = $row['Option_3'];
   $row_array['options'][] = $row['Option_4'];
}

Also, remember that mysql_ functions are deprecated

edited May 23 '17 at 11:51

Community

1
1

answered Apr 19 '15 at 19:05

Machavity

30,841
27
92
100

Thanks. I got it now. Also I am trying to upgrade myself (regarding mysql), but it's taking time. I will accept the answer, once the stackoverflow's time limit is over. For now an upvote :) – Dr. Atul Tiwari Apr 19 '15 at 19:14

score 0 · Answer 2 · answered Apr 19 '15 at 19:14

Try this

<?php
$result = mysql_query("select * from test_table limit 5", $db);  
$json_response = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$ans = array();
$ans["question"]= $row["question"];
$ans["options"]=$row;
$json_response[]=$ans;
}
echo json_encode($json_response);
?>

This will generate same result as you expect but in the option array you will get question fields too. But that can be ignored while working on json result.

Note:- Mysql functions are deprecated in newer versions.

How to convert Strings from MySql to JSON array

2 Answers2

Linked