JSON encode MySQL results How do we use the json_encode() function with MySQL query results Do we need to iterate through
PROBLEM
How do we use the json_encode() function with MySQL query results?
Do we need to iterate through the rows or can we just apply it to the entire results object?
SOLUTION :1
Sql Code
$sth = mysqli_query("SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);
The function json_encode needs PHP >= 5.2 and the php-json package – as mentioned here
NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.
SOLUTION :2
Try this, this will create your object properly
Sql Code
$result = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($result)) {
$rows['object_name'][] = $r;
}
print json_encode($rows);
SOLUTION :3
Sql Code
$sqldata = mysql_query("SELECT * FROM `$table`");
$rows = array();
while($r = mysql_fetch_assoc($sqldata)) {
$rows[] = $r;
}
echo json_encode($rows);
SOLUTION :4
Before you name the root-element in the array to something, we have not been able to access anything in the final json before that.
Sql Code
$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows['root_name'] = $r;
}
print json_encode($rows);
SOLUTION :5
The code below works fine here!
Php Code
<?php
$con=mysqli_connect("localhost",$username,$password,databaseName);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query = "the query here";
$result = mysqli_query($con,$query);
$rows = array();
while($r = mysqli_fetch_array($result)) {
$rows[] = $r;
}
echo json_encode($rows);
mysqli_close($con);
?>
SOLUTION :6
Sql Code
if ($result->num_rows > 0) {
# code...
$arr = [];
$inc = 0;
while ($row = $result->fetch_assoc()) {
# code...
$jsonArrayObject = (array('lat' => $row["lat"], 'lon' => $row["lon"], 'addr' => $row["address"]));
$arr[$inc] = $jsonArrayObject;
$inc++;
}
$json_array = json_encode($arr);
echo $json_array;
}
else{
echo "0 results";
}
SOLUTION :7
Here the simple fix to stop it putting speech marks around numeric values…
Sql Code
while($r = mysql_fetch_assoc($rs)){
while($elm=each($r))
{
if(is_numeric($r[$elm["key"]])){
$r[$elm["key"]]=intval($r[$elm["key"]]);
}
}
$rows[] = $r;
}
SOLUTION :8
we could simplify Paolo Bergantino answer like this
Sql Code
$sth = mysql_query("SELECT ...");
print json_encode(mysql_fetch_assoc($sth));
Add Comment