Make json array from mysql query

Question

I am trying the following code:

$order = array(); $imageURL = array(); $name = array();
        while($row = mysql_fetch_array($mysql->result)) {
            $order[] = $row["order"]; 
            $imageURL[] = $row["imageURL"];
            $name[] = $row["name"];
        }
        $res = array($order, $imageURL,$name);
        return json_encode($res);

But it is not outputting in json format, any ideas?

Output:

[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30"],["previews\/en-1-1.gif","previews\/en-1-2.gif","previews\/en-1-3.gif","previews\/en-1-4.gif","previews\/en-1-5.gif","previews\/en-1-6.gif","previews\/en-1-7.gif","previews\/en-1-8.gif","previews\/en-1-9.gif","previews\/en-1-10.gif","previews\/en-1-11.gif","previews\/en-1-12.gif","previews\/en-1-13.gif","previews\/en-1-14.gif","previews\/en-1-15.gif","previews\/en-1-16.gif","previews\/en-1-17.gif","previews\/en-1-18.gif","previews\/en-1-19.gif","previews\/en-1-20.gif","previews\/en-1-21.gif","previews\/en-1-22.gif","previews\/en-1-23.gif","previews\/en-1-24.gif","previews\/en-1-25.gif","previews\/en-1-26.gif","previews\/en-1-27.gif","previews\/en-1-28.gif","previews\/en-1-29.gif","previews\/en-1-30.gif"],["Helasd you?","Where sasaddsdam?","Weasdd!","Tasasdther","AtsaddsaBeach","Cheasd Hotel","At the Hotel","aaaaaaaaat?","At the Market","Aasdt's","Mesadasd th","Shdsdsssg","sssss","On aaaa","Do you work or study?","aaaaaaa","At tadstation","aaaaaae Gym","How doasdto\u2026?","Planning a Trip","At adsk","At the asdurant","At the Inads00e9","My Taaaog","A Meetiaaaaay adher","Tourist sdsadn Centre","Saaaaing","Aaaaaa Match","Lookaaasd","At tasda"]]

Are you returning from a function? If not, try outputting it instead using `echo` or `print` — scrowler, Jun 26 '14 at 22:13
What are you getting? are you sure the query result is not empty, and why split the array? and change return for echo — Emilio Gort, Jun 26 '14 at 22:13
You're also calling `mysql_fetch_array()` proceduarlly while using an object as your query resource... While this is not necessarily incorrect it's definitely weird and most likely isn't what you're meaning to do... — scrowler, Jun 26 '14 at 22:14
It seems this can be answered here -- that is, if you are outputting to JavaScript. http://stackoverflow.com/questions/8373315/is-there-a-way-to-pass-multiple-arrays-to-php-json-encode-and-parse-it-with-jque — TheLettuceMaster, Jun 26 '14 at 22:16
It is outputting json: An array of arrays. What do you want / expect, objects? — jeroen, Jun 26 '14 at 22:18
i want some kind of multi dimensial array so look up 1 and get the order etc. values — Jake, Jun 26 '14 at 22:20
@Jake You have an array available in php in the `$res` variable. If you need to access the values in another language, you would need to parse the json first in that language. Note that json is a string, not an object or array. — jeroen, Jun 26 '14 at 22:23
@Jake - that output is as expected from your code. If you want an **object**, meaning you will have references like key value pairs in PHP associative arrays, why don't you add `$row` to an output array in each iteration *as a whole*, then encode that, i.e. the complete database output you're returning — scrowler, Jun 26 '14 at 22:34

score 0 · Answer 1 · answered Jun 26 '14 at 23:01

I think you're just after a better format to work with in your output, because you've currently set it up with three arrays and you'll have to reference each record's properties across these arrays by a numeric key.

I've reconstructed an example of what your original database output would look like:

$array = json_decode($json, true);

$row = array();
list($orders, $imageURLS, $names) = $array;
foreach($orders as $key => $val) {
  $row[] = array(
    'order' => $val,
    'imageURL' => $imageURLS[$key],
    'name' => $names[$key]
  );
}

So in your code, you should try this:

$output = array();
while($row = mysql_fetch_array($mysql->result)) {
    $output[] = $row;
}
return json_encode($output);

And you'll get a much easier data structure to work with. Example:

[
  {
    "order": "1",
    "imageURL": "previews\/en-1-1.gif",
    "name": "Helasd you?"
  },
  {
    "order": "2",
    "imageURL": "previews\/en-1-2.gif",
    "name": "Where sasaddsdam?"
  }
]

score 0 · Answer 2 · answered Jun 26 '14 at 23:02

Judging by the comments to your original question, it looks like you're looking for :

 $main_array = array() ;    


    while($row = mysql_fetch_array($mysql->result)) {

        $res = array($row["order"], $row["imageURL"],$row["name"]);   
        $main_array[] = $res; 
    }

    return json_encode($main_array);

score 0 · Answer 3 · answered Jun 26 '14 at 23:03

The output is correct. A json array, containing three json arrays. But I guess you want each element to be a separate array/object, containing order, imageURL, name. If that is correct, try something like:

$result = array();

while($row = mysql_fetch_array($mysql->result)) {
$result[] = array("order"=>$row["order"], "imageURL" => $row["imageURL"],"name"=> $row["name"])
}
$res = array($order, $imageURL, $name);
return json_encode($result);

Make json array from mysql query

3 Answers3