CODEWITHSUNDEEP
phpmysqljson
CaribouCode
CaribouCode

Reputation: 14398

Mysql fetch all rows and echo as json

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.

Upvotes: 4

Views: 40601

Answers (5)

Manomite
Manomite

Reputation: 349

$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);

Upvotes: -1

BlueMystic
BlueMystic

Reputation: 2297

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.

Upvotes: 3

Schleis
Schleis

Reputation: 43700

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

Upvotes: 15

stefreak
stefreak

Reputation: 1450

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 :)

Upvotes: 2

dcbarans
dcbarans

Reputation: 493

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

Upvotes: 0

Related Questions