Json Encode MySQL Results

php json_encode mysql result

$sql = mysql_query("SELECT * FROM item_details WHERE posting_id='$item_number'");
$results = array();
while($row = mysql_fetch_array($sql))
{
   $results[] = array(
      'title' => base64_decode($row['title']),
      'price' => $row['price'],
      'seller_user' => $row['user']
   );
}
$json = json_encode($results);

JSON encode MySQL results

$sth = mysqli_query($conn, "SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package - as mentioned here

NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.

Return JSON object from MySQL query using json_encode

I changed my query to the following:

SELECT DISTINCT questions.question_text, answers.answer_text 
   FROM     questions, answers, test
   WHERE    questions.question_id = answers.question_id
   AND      questions.test_id =

The while loop to this:

while($row = mysqli_fetch_assoc($result)) {
    $encode[$row['question_text']][] = $row['answer_text'];
}

This gave me this:

{
    "What is HTML?": [
        "HTML is a Hypertext Markup Language",
        "HTML is a food",
        "HTML is an Asynchronous language",
        "HTML is a styling language"
    ]
}

Which I can now work with.

MySQL to JSON Encode Error / No Results

after doing @RhinoDevel's suggestion, also do this

while($row =mysqli_fetch_assoc($result))
{
     array_push($emparray,$row);
}

EDIT : following comments from Rhino,

    $array[] = array ();
    for ($i = 0 ; $i < 5 ; $i++) {
        $array[] = $i;
    }
    $logger->info("OP : " . json_encode($array));
    $array = array ();
    for ($i = 0 ; $i < 5 ; $i++) {
        $array[] = $i;
    }
    $logger->info("RD : " . json_encode($array));

    $array = array ();
    for ($i = 0 ; $i < 5 ; $i++) {
        array_push($array , $i);
    }
    $logger->info("YL : " . json_encode($array));

yields :

2015-07-17T06:59:42-04:00 TestPatient.api           INFO  OP : [[],0,1,2,3,4]
2015-07-17T06:59:42-04:00 TestPatient.api           INFO  RD : [0,1,2,3,4]
2015-07-17T06:59:42-04:00 TestPatient.api           INFO  YL : [0,1,2,3,4]

How to convert result table to JSON array in MySQL

New solution:

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

---

Old solution:

With help from @Schwern I managed to put up this query, which seems to work!

SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
) 
FROM person;

Two MySQL output in single JSON encode in PHP

1. How about using UNION in your query? Please check it out here: https://dev.mysql.com/doc/refman/5.0/en/union.html

2. What about

   $fullOutput = array_merge($output1, $output2);
   echo json_encode($fullOutput);

Json encode an entire mysql result set

If I were you, I would not json_encode each individual array, but merge the arrays together and then json_encode the merged array at the end. Below is an example using 5.4's short array syntax:

$out = [];
while(...) {
  $out[] = [ 'id' => $i, 'name' => $row['name'] ];
}
echo json_encode($out);

---

Related Topics