0

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 []

user3057706
  • 73
  • 1
  • 6
  • 1
    Just to be safe, have you verified that your query is returning data? – Anil Dec 07 '13 at 19:21
  • Have a look at this: http://stackoverflow.com/questions/3430492/convert-mysql-record-set-to-json-string-in-php – Severin Dec 07 '13 at 19:22
  • 2
    Please **don't use `mysql_*` functions anymore**, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. Instead you should learn about [prepared statements](http://bobby-tables.com/php.html) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you pick PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). – Marcel Korpel Dec 07 '13 at 19:23

4 Answers4

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.

Peter van der Wal
  • 11,141
  • 2
  • 21
  • 29
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
eaglewu
  • 458
  • 5
  • 12
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"}]
cjungel
  • 3,701
  • 1
  • 25
  • 19
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));
user3057706
  • 73
  • 1
  • 6