Creating json from sql query with an additional key/value pair using PHP

Question

Disclaimer: I'm fairly new to php.

I am trying to create an array out of a query witch could return multiple results. Additionally, I am doing a distance calculation and adding that value to the array, creating a json string, then returning it to my javascript.

I can create the array with the data from the database, and insert the additional value into the array, and return it, but when I try with a query witch returns more than one result, I'm only getting the last result back, because the array is being over written. I've tried to the array_merge function, but I'm still only getting the last one back.

Here is what I have so far:

$positionquery = "select d.code, d.lon, d.lat from table1 as d where d.lon <> 0 and d.lat <> 0 and d.brand = 'THE_BRAND'";

$results = mysql_query($positionquery) or die ('Query Failed" ' . mysql_error());

if(mysql_num_rows($results) > 0){
    while($row = mysql_fetch_assoc($results)){
        $locations = $row;
        $tempdistance = distance($lat, $lon, $locations['lat'], $locations['lon'], 'M');
        if($tempdistance <= 150){
            $secondquery = 'SELECT * FROM table1 as d left join table2 as p on p.dealer_name = d.dealer_name left join table3 as o on o.retailercode = d.cicode  where d.cicode ="'.$locations['cicode'].'"';

            $queryResults = mysql_query($secondquery) or die('Query Failed ' . mysql_error());

            $tempArray = mysql_fetch_assoc($queryResults);
                $tempArray['distance'] = $tempdistance;

                $returnArray = array_merge($returnArray,$tempArray);
        }
    }
     echo json_encode($returnArray);
}

Any help would be greatly appreciated.

Please dont use mysql_* functions. Try mysqli or PDO

HddnTHA
– HddnTHA

2014-06-24 20:05:13 +00:00
Commented Jun 24, 2014 at 20:05 — HddnTHA
– HddnTHA, Commented Jun 24, 2014 at 20:05

Barmar · Accepted Answer · 2014-06-24 19:56:24Z

1

You're not creating an array of results, you're overwriting the $returnArray variable with each row of the results. It should be:

$returnArray = array();
while ($row = mysql_fetch_assoc($results)) {
    ...
    $returnArray[] = $tempArray;
}
echo json_encode($returnArray);

answered Jun 24, 2014 at 19:56

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating json from sql query with an additional key/value pair using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related