从MySQL中的另一个查询调用查询，并只获取1个JSON

Question

我想从另一个表调用和选择查询到另一个查询，并只发送一个JSON。我得到了2个表(towing_list和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"}]

我想使用"towing_username"并从另一个表中调用他们的详细信息，希望是他们的"towing_fullname"one_answers"towing_contactnumber"，所以它将得到下面的json结果:

[{"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"}]

我的表

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

这是我的代码的一部分

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

是可能的吗?我是JSON的新手。请帮. .

Answer 1

可以使用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 ";