Mysql fetch all rows and echo as json

Question

I've got a database with 5 columns and multiple rows. I want to fetch the first 3 rows and echo them as an array. So far I can only get the first row (I'm new to PHP and mysql). Here's my PHP so far:

//==== FETCH DATA
$result = mysql_query("SELECT * FROM $tableName");
$array = mysql_fetch_row($result);    

//==== ECHO AS JSON
echo json_encode($array);

Help would be much appreciated.

[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/q/12859942). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). — Gottlieb Notschnabel, Nov 14 '14 at 23:39
Also note that mysql supports the datatype JSON since mysql 5.7.8 — Blablaenzo, Apr 06 '16 at 22:52

score 15 · Accepted Answer · edited Jun 21 '13 at 17:41

You need to loop through the results. mysql_fetch_row gets them one at a time.

http://php.net/manual/en/function.mysql-fetch-row.php

The code would end up like:

$jsonData = array();
while ($array = mysql_fetch_row($result)) {
    $jsonData[] = $array;
}
echo json_encode($jsonData);
//json_encode()

PLEASE NOTE The mysql extension is deprecated in PHP 5.5, as stated in the comments you should use mysqli or PDO. You would just substitute mysqli_fetch_row in the code above. http://www.php.net/manual/en/mysqli-result.fetch-row.php

score 3 · Answer 2 · answered Jul 23 '14 at 18:06

I do like this while quering an ODBC database connection with PHP 5.5.7, the results will be in JSON format:

$conn = odbc_connect($odbc_name, 'user', 'pass');
$result = odbc_exec($conn, $sql_query);

Fetching results allowing edit on fields:

while( $row = odbc_fetch_array($result) ) { 
     $json['field_1'] = $row['field_1'];
     $json['field_2'] = $row['field_2'];
     $json['field_3'] = $row['field_1'] + $row['field_2'];

     array_push($response, $json); 
  }

Or if i do not want to change anything i could simplify like this:

while ($array = odbc_fetch_array($result)) { $response[] = $array; }

What if i want to return the results in JSON format?, easy:

echo json_encode($response, true);

You can change odbc_fetch_array for mysqli_fetch_array to query a MySql db.

score 2 · Answer 3 · answered Mar 19 '13 at 22:11

According to the PHP Documentation mysql_fetch_row (besides that it's deprecated and you should use mysqli or PDO)

Returns a numerical array that corresponds to the fetched row and moves the internal data pointer ahead.

so you need for example a while loop to fetch all rows:

$rows = array();

while ($row = mysql_fetch_row($result)) {
    $rows[] = $row;
}

echo json_encode($rows);

I leave it to you how to only fetch 3 rows :)

score 0 · Answer 4 · answered Mar 19 '13 at 22:07

0

You need to put this in some kind of a loop, mysql_fetch_row returns results one at a time.

See example: http://www.php.net/manual/en/mysqli-result.fetch-row.php#example-1794

answered Mar 19 '13 at 22:07

dcbarans

482
7
14

score -1 · Answer 5 · answered Dec 31 '17 at 14:46

-1

$result = mysql_query( "SELECT * FROM $tableName ORDER BY id LIMIT 3");

$json = array(); while($array = mysql_fetch_row($result)){

$json[] = $array; }

echo json_encode($json);

answered Dec 31 '17 at 14:46

Manomite

199
1
5

try to explain your answer instead of using code only – Peter Wilson Dec 31 '17 at 14:52

Mysql fetch all rows and echo as json

5 Answers5

Linked