PHP Creating JSON array list (data coming from MySQL)

Question

I am a novice in Json and I am not able to fill json list with data gathered from mysql query. Only one value is filled in the list and it keeps repeating rather than showing all the values. The code is:

<?php

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db('abcd');
$jsonData = 0;

$result = mysql_query("SELECT picname,title,date,time,location,timestamp FROM uploaded_photo_data ORDER BY timestamp DESC ");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
if (mysql_num_rows($result) > 0) {

    $num = mysql_num_rows($result);

    while ($row = mysql_fetch_assoc($result)) {
    $a = $row['location']."/".$row['picname']; 
        echo $row['timestamp'];
        echo $a;
        echo $row['picname'];
        echo $row['title'];
        echo $row['location'];
        echo "<br></br>";

        $dir = $row['location']."/"; 
        $jsonData = '{';     
        $x = 0;
        $dirHandle = opendir($dir);
        while($x!=$num){
            $x++;
            $jsonData .= '"img'.$x.'":{ "num":"'.$x.'","title":"'.$row['title'].'","src":"'.$a.'", "timestamp":"'.$row['timestamp'].'"},<br></br> ';
        }

    }
}
closedir($dirHandle);
        $jsonData = chop($jsonData, ",");
        $jsonData .= '}'; 
        echo $jsonData; echo $x;

This code has no logic. You create $jsonData variable inside first while loop, then in the same loop (still dealing with first row) you iterate the number of rows times and fill the $jsonData. Then again and again, while $jsonData are always overwritten. Your $jsonData will in the end contain last database result row n times, where n is number of results. My advice, start from scratch. — klimpond
– klimpond, Commented Mar 20, 2014 at 8:09

smiggle · Accepted Answer · 2014-03-20 08:13:11Z

4

You can encode an array to JSON with the json_encode method.

<?php
$arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);

echo json_encode($arr);
?>

The above example will output:

{"a":1,"b":2,"c":3,"d":4,"e":5}

php.net/manual/en/function.json-encode.php

EDIT:

Maybe this works for you:

$jsonData = array();
while ($row = mysql_fetch_assoc($result)) {
    $a = $row['location']."/".$row['picname']; 
    echo $row['timestamp'];
    echo $a;
    echo $row['picname'];
    echo $row['title'];
    echo $row['location'];
    echo "<br></br>";

    $dir = $row['location']."/";    
    $x = 0;

    while($x!=$num){
        $x++;
        $img = 'img'.$x;
        $jsonData[$img] = array(
            "num" => $x,
            "title" => $row['title'],
            "src" => $a,
            "timestamp" => $row['timestamp']
        );
    }
}

print json_encode($jsonData);

edited Mar 20, 2014 at 8:13

answered Mar 20, 2014 at 8:03

smiggle

1,2596 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Md. Sahadat Hossain Over a year ago

perfect answer. But it will be better if you solve this issue by using code form questions.

Collectives™ on Stack Overflow

PHP Creating JSON array list (data coming from MySQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related