encoding data from MySql to JSON

Question

I have mysql database online on my domain, and I have a simple table called "Locations" with some data in it, I want to call in the data in an array, then encode the array with json and finally echo out the encoded json data. Here's my code, my problem in is putting data in array and encoding it to jso:

<?php
// obtaining connection : successful!
$con = mysql_connect("myhost", "username", "password");
$con or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

// selecting all from table "locations" : successful!
$result = mysql_query("SELECT * FROM Locations") 
or die(mysql_error()); 

// puting data into array and encode it with json : FAILED! 
$resultArray = array();
$tempArray = array();

// Loop through each row in the result set
while($row = $result->fetch_object())
{
    // Add each row into our results array
    $tempArray = $row;
    array_push($resultArray, $tempArray);
}

// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);

// closing connection : successful!
mysql_close($con);
?>

I run my code and heres what I get :

This is what I get when launching code above

Thank you guys.

EDITED:

I changed to mysqli, now it looks like this:

<?php

// Create connection
$con=mysqli_connect("host","user","pass","dbname");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// This SQL statement selects ALL from the table 'Locations'
$sql = "SELECT * FROM Locations";

// Check if there are results
if ($result = mysqli_query($con, $sql))
{
// If so, then create a results array and a temporary one
// to hold the data
$resultArray = array();
$tempArray = array();

// Loop through each row in the result set
while($row = $result->fetch_object())
{
    // Add each row into our results array
    $tempArray = $row;
    array_push($resultArray, $tempArray);
}

// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);
}

// Close connections
mysqli_close($result);
mysqli_close($con);
?>

still getting exact same error

this is a 500 server error.. have you checked your logs / Also side note, you should not use mysql_ you should upgrade to mysqli_ — Pogrindis, Jul 04 '14 at 10:42
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Jul 04 '14 at 10:43

score 1 · Accepted Answer · answered Jul 04 '14 at 10:51

1

Please try following code: I thing this work for you.

$con = mysql_connect("host", "username", "password");
$con or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

// selecting all from table "locations" : successful!
$result = mysql_query("SELECT * FROM Location") 
or die(mysql_error()); 

// puting data into array and encode it with json : FAILED! 
$resultArray = array();
$tempArray = array();

// Loop through each row in the result set
while($row = mysql_fetch_assoc($result))
{
    // Add each row into our results array
    $tempArray = $row;
    array_push($resultArray, $tempArray);
}

// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);

// closing connection : successful!
mysql_close($con);

answered Jul 04 '14 at 10:51

MRJethva

357
4
15

just a note, answers where you explain WHAT the problem is are more appreciated. You should extend your answer by pointing out the mistake. – Pogrindis Jul 04 '14 at 11:22
@Pogrindis I suppose the problem was that `mysql_query()` returns a resource and not an instance, so that's why the successive call `$result->fetch_object()` didn't work, and instead a mysql_* function as `mysql_fetch_assoc($result)` do it. – ilpaijin Jul 04 '14 at 11:44
@ilpaijin i understand the difference, but the OP did not. Hence the question. Some advice on where he went wrong in the answer is always nice, not just the blurb of code. Might just be personal preference though. – Pogrindis Jul 04 '14 at 11:54
Ok Ok however I agree with you. – ilpaijin Jul 04 '14 at 11:59

score 0 · Answer 2 · edited Jul 04 '14 at 11:07

0

Change this line:

while($row = $result->fetch_object())

to:

while($row = mysql_fetch_assoc($result))

edited Jul 04 '14 at 11:07

idlefingers

31,659
5
82
68

answered Jul 04 '14 at 10:51

Latheesan

23,247
32
107
201

encoding data from MySql to JSON

2 Answers2