How to JSON encode multiple rows from SQL statement using PHP

Question

I'm in the process of building an iOS app that uses a webservice for data. The webservice consists of PHP, MySQL database.

I've successfully managed to JSON encode the data returned, but the code I am using only seems to encode 1 row.

I wanted to get some advice on how to encode multiple rows?

Thanks.

<?php
$username = "root";
$password = "*******";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

//print "Connected to MySQL<br>";

$selected = mysql_select_db("newtest",$dbh)
or die("Could not select first_test");

$query = "SELECT * FROM MyGuests ";
$result=mysql_query($query);

echo json_encode(mysql_fetch_assoc($result));

?>

You are only fetching one row of the result set. You need to fetch them all. — Mike Brant
– Mike Brant, Commented Nov 25, 2014 at 15:48
Please, don't use mysql_* functions, They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi. — Jay Blanchard
– Jay Blanchard, Commented Nov 25, 2014 at 15:55
Possible duplicate of How to encode json with multiple rows? — Beulah Akindele
– Beulah Akindele, Commented Aug 10, 2018 at 18:12

Dima · Accepted Answer · 2014-11-25 15:43:38Z

2

$query = "SELECT * FROM MyGuests ";
$result=mysql_query($query);
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
  $rows[] = $r;
}
print_r json_encode($rows);

answered Nov 25, 2014 at 15:43

Dima

8,6524 gold badges31 silver badges60 bronze badges

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

2 Comments

Jay Blanchard Over a year ago

You're mixing mysql_ and mysqli_ functions. That will not work.

freshfanatic Over a year ago

Hi, thanks for this. I've implemented this change, and previously when accessing the URL for this query on a browser it displayed the single row as JSON, but not the page shows blank. Have I done something wrong or is this expected? Sorry for the noob questions!

Collectives™ on Stack Overflow

How to JSON encode multiple rows from SQL statement using PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related