CODEWITHSUNDEEP
phpmysqlarraysjson
Purvesh Bhavsar
Purvesh Bhavsar

Reputation: 43

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

Upvotes: 4

Views: 539

Answers (3)

Doug
Doug

Reputation: 1890

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

Upvotes: 2

ajay ramgarhia
ajay ramgarhia

Reputation: 118

you have mysql query syntax error. Intead of table1,field2 use table1.field2

Upvotes: 0

B. Desai
B. Desai

Reputation: 16436

Your query is wrong. change it to

SELECT table1.field1, table1.field2, table1.field3, table2.field4 FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1

need table1.field2 instead of table1,field2

Upvotes: 1

Related Questions