Zubir Zubir - 1 month ago 6
JSON Question

Call query from another query in MySQL and fetch into only 1 JSON

I want to call and select query to another query from another table and send in only one JSON. I got 2 tables (

towing_list
and
towing_info
)

json get method

[{"towing_id":"51","towing_username":"tow","towing_latitude":"3.7310769",
"towing_longitude":"103.1240930","distance":"0"},
{"towing_id":"56","towing_username":"tow1","towing_latitude":"3.7311311",
"towing_longitude":"103.1239854","distance":"0.013374089073083037"}]


i want to use "towing_username" and call their detail from another table wish are their "towing_fullname" and "towing_contactnumber" so it will get this json result below:

[{"towing_id":"51","towing_username":"tow","towing_fullname":"tow_name",
"towing_contactnumber":"0123456789","towing_latitude":"3.7310769",
"towing_longitude":"103.1240930","distance":"0"},
{"towing_id":"56","towing_username":"tow1","towing_fullname":"tow1_name",
"towing_contactnumber":"01518191904","towing_latitude":"3.7311311",
"towing_longitude":"103.1239854","distance":"0.013374089073083037"}]


My Table

towing_list : (towing_id,towing_username,towing_latitude,towing_longitude)
towing_info : (towing_id,towing_username,towing_fullname,towing_contactnumber)


this is part of my code

$q = "

SELECT * , (
6371 * acos (
cos ( radians($lat) )
* cos( radians( towing_latitude ) )
* cos( radians( towing_longitude ) - radians($lon) )
+ sin ( radians($lat) )
* sin( radians( towing_latitude ) )
) ) AS distance FROM towing_list WHERE `towing_status`='$status' HAVING distance < $total_dis_miles

ORDER BY distance LIMIT 0 , 20 ";

$r = mysql_query($q);

while ($row=mysql_fetch_object($r)) { $array[]=$row; }

echo json_encode($array);


Is it possible to do?. I m new to JSON. Please help..

Answer

you can use join

$q = "SELECT * , ( 6371 * acos (
cos ( radians($lat) ) * cos( radians( towing_latitude ) ) * cos(radians( towing_longitude ) - radians($lon) ) + sin ( radians($lat) ) * sin( radians( towing_latitude ) ) ) ) AS distance FROM towing_list INNER JOIN towing_info ON towing_info.towing_id = towing_list.towing_id WHERE towing_status='$status' HAVING distance < $total_dis_miles

ORDER BY distance LIMIT 0 , 20 ";