Purvesh Bhavsar Purvesh Bhavsar - 1 year ago 70
JSON Question

Convert Large number of mysql records to json using php

I want to convert LARGE NUMBER of MySQL data to JSON using PHP. I have 20K and above records but I am unable to convert MySQL data to JSON. I want to create REST API so need to send(response) data in JSON format.

I have tried this but not getting output:

Code:


$query = mysql_query("SELECT table1.field1, table1,field2, table1.field3, table2.field4 FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1");

$result = array();
while($row = mysql_fetch_assoc($query))
{
$result[] = array('Name' => $row["field1"], 'Last Name' => $row['field2'], 'country' => $row["field3"], 'location' => $row["field4"]);
}

echo json_encode($result);

Answer Source

Asides from the error with your query which I'm assuming is just a typing error in your question because no one labels their columns as field1, field2 etc.

The issue you are facing is more than likely an encoding issue. Try the following which encodes the result as UTF8 and will hopefully result in valid JSON.

mysql_set_charset ("UTF8");
$query = mysql_query("SELECT table1.field1, table1field2, table1.field3, table2.field4 FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1");

$result = array();

while($row = mysql_fetch_assoc($query))
{

    $result[] = array('Name' => $row["field1"], 'Last Name' => $row['field2'], 'country' => $row["field3"], 'location' => $row["field4"]);
}

echo json_encode($result);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download