MySQL to JSON in PHP

Question

I have a basic MySQL database table People with 3 columns (ID, Name, Distance)

I am trying to output this with PHP as a JSON so my web app can pick it up. I tried using the solution from another answer:

$sth = mysql_query($con,"SELECT * FROM People");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

However I am just returning a blank array of [].

Update: Changed to mysqli and added dumps:

$sth = mysqli_query("SELECT * FROM Events",$con);
$rows = array();
var_dump($sth);
while($r = mysqli_fetch_assoc($sth)) {
var_dump($rows); 
$rows[] = $r;
}
print json_encode($rows);

Returns:

NULL []

Just to be safe, have you verified that your query is returning data? — Anil
– Anil, Commented Dec 7, 2013 at 19:21
Have a look at this: stackoverflow.com/questions/3430492/… — Severin
– Severin, Commented Dec 7, 2013 at 19:22
Please don't use mysql_* functions anymore, they are deprecated. See Why shouldn't I use mysql_* functions in PHP? for details. Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is a good tutorial. — Marcel Korpel
– Marcel Korpel, Commented Dec 7, 2013 at 19:23

Peter van der Wal · Accepted Answer · 2013-12-07 19:23:34Z

4

Swap your query and connection in the mysql_query-statement:

$sth = mysql_query("SELECT * FROM People", $con);

Besides that, the mysql-library is deprecated. If you're writing new code consider using mysqli_ or PDO.

answered Dec 7, 2013 at 19:23

Peter van der Wal

12k2 gold badges24 silver badges29 bronze badges

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

2 Comments

Peter van der Wal Over a year ago

Depends, if you have more than one connection (I guess he doesn't) you should provide it

Marcel Korpel Over a year ago

Nope, mysql_query will just use the last connection. Anyway, the OP should stop using the mysql_* functions.

eaglewu · Accepted Answer · 2013-12-07 19:29:39Z

0

Why no mysql_connect ?

var_dump($sth);   // check the return of mysql_query()
var_dump($rows);  // check the array of mysql_fetch_assoc result

answered Dec 7, 2013 at 19:29

eaglewu

4705 silver badges13 bronze badges

Comments

cjungel · Accepted Answer · 2013-12-07 19:42:38Z

0

I would check if your query is returning any rows first.

<?php
$rows = array();

array_push($rows, array("name" => "John"));
array_push($rows, array("name" => "Jack"));
array_push($rows, array("name" => "Peter"));

print  json_encode($rows);
?>

Produces this output.

[{"name":"John"},{"name":"Jack"},{"name":"Peter"}]

answered Dec 7, 2013 at 19:42

cjungel

3,7911 gold badge27 silver badges20 bronze badges

Comments

user3057706 · Accepted Answer · 2013-12-07 21:59:04Z

0

Solved using a combination of answer to get:

$sth = mysqli_query($con,"SELECT * FROM Events");
$datarray = array();
while($row = mysqli_fetch_assoc($sth)) {
$datarray[] = $row;
}
print_r(json_encode($datarray));

answered Dec 7, 2013 at 21:59

user3057706

731 silver badge6 bronze badges

Collectives™ on Stack Overflow

MySQL to JSON in PHP

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related