Creating a JSON result from mysql

Question

How to create this type of json?:

{
     "Southern", "1040",
     "South-West": "710",
     "South-East": "692",
     "Western": "638",
     "North-Western", "448",
     "Eastern": "80",
     "North-East": "9"
}

I tried this way but you do not get that you need:

$result = mysqli_query($db,"query"); 

       $json_response = array();

        while ($row = mysqli_fetch_array($result, MYSQL_NUM)) {
            array_push($json_response,array($row[0],$row[1]));
        }

What's the difference between your output and the desired output? — Celeo
– Celeo, Commented Dec 3, 2014 at 22:37
Are you talking about the format not being right? What result are you getting? — Phil Tune
– Phil Tune, Commented Dec 3, 2014 at 22:38
You're mixing MySQL APIs with mysqli_query and MYSQL_NUM — Funk Forty Niner
– Funk Forty Niner, Commented Dec 3, 2014 at 22:39
Receives this: ([["Southern","1040"],["South-West","710"],["South-East","695"],["Western","638"],["North-Western","448"],["Eastern","80"],["North-East","9"]]) How to convert to the correct json? — Sebastiano
– Sebastiano, Commented Dec 3, 2014 at 22:46

SaidbakR · Accepted Answer · 2014-12-03 23:42:55Z

2

Simply get an array from your query and encode it:

$result = mysqli_query($db,"query"); 
$json_response = mysqli_fetch_all($result, MYSQLI_ASSOC);
echo json_encode($json_response);
// OR
echo json_encode($json_response,JSON_FORCE_OBJECT);

For reference:

mysqli.query It returns an object, mysqli-result object, then use mysqli_fetch_all() that returns an array which is encoded by json_encode function.

edited Dec 3, 2014 at 23:42

answered Dec 3, 2014 at 22:49

SaidbakR

13.6k23 gold badges113 silver badges202 bronze badges

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

7 Comments

Sebastiano Over a year ago

This way I know. I would like to get the JSON in the form...

{      "Southern":"1040",      "South-West": "710",      "South-East": "692",      "Western": "638",      "North-Western", "448",      "Eastern": "80",      "North-East": "9" }

SaidbakR Over a year ago

As I understand, Southern, for example, is a field in your database table and 1040 is its value. So, the way I regard should give the result you want.

Sebastiano Over a year ago

The result of your code is as follows:

[["Southern","1040"],["South-West","710"],["South-East","695"],["Western","638"‌],["North-Western","448"],["Eastern","80"],["North-East","9"]]

SaidbakR Over a year ago

Well, you may use JSON_FORCE_OBJECT as a parameter in json_encode()

Sebastiano Over a year ago

This method does not work on the server:

if($db = @mysqli_connect('mysql01xxxx','log','pass')){ 	      mysqli_select_db($db, '_mybase');         		$result = mysqli_query($db,"query");  		$json_response = $result->fetch_all();	 } 	else{ 		die('Could not connect to db: ' . mysql_error()); 	}		 		mysqli_close($db); 		echo json_encode($json_response);

|

AbraCadaver · Accepted Answer · 2014-12-03 23:47:50Z

1

I assume from your attempt that each row has 2 columns with the data for example?

column1        column2
"southern"     "1040"

If so, just tweak how you build the array:

    $result = mysqli_query($db,"query"); 

    while ($row = mysqli_fetch_array($result, MYSQL_NUM)) {
        $data[$row[0]] = $row[1];
    }
    $json_response = json_encode($data);

answered Dec 3, 2014 at 23:47

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Collectives™ on Stack Overflow

Creating a JSON result from mysql

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related