>我有2个表用户(id,userid,fn,ln)和userdetails(id,userid,image,history,location,activity)我已经为第一个表编写了一个查询来检索所有数据,我只想要第二个表中的历史记录和位置。
我已经检索了数组并将其发送给json_encode。现在我想检索历史记录,位置并创建一个新的键历史记录,我想将历史位置值添加到历史记录键中。
我需要这些查询和 json 格式。
-
对于我需要检索的特定用户是自己的历史记录在两个表中共有的用户 ID提前致谢
$sth = mysql_query("从用户中选择 * ");$result = mysql_fetch_assoc($sth); $i=0; foreach($result as $data) { $final_array[$i]['id'] = $data['id']; $final_array[$i]['email'] = $data['email']; $final_array[$i]['fname'] = $data['fname']; $final_array[$i]['lname'] = $data['lname'];
$sth2 = mysql_query("SELECT id,places,act FROM user_dates WHERE user_id= '".$data['email']."'"); $result2 = mysql_fetch_assoc($sth2); $j=0; $history_array = array(); foreach($result2 as $data2) { $history_array[$j] = array("id" => $data2['id'],"places" => $data2['places'], "act " => $data2['act ']); $j++; } $final_array[$i]['history'] = $history_array; $i++; } echo json_encode($final_array);
[ { "id": "81", "user_id": "2011", "fn": "asd.", "ln": "wer", "历史":[ { "id": "350", "历史": "make1", "位置": "qwe" } ] }, { "id": "82", "user_id": "2012", "fn": "asd1", "ln": "wer1", "历史":[ { "id": "350", "历史": "make2", "位置": "qwe2" } ] }]
Userdetails
表包含每个用户的multiple records
。所以你需要做子查询并得到结果,然后形成一个mulch-dimensional array
。最后,encode as a JSON
.
$sth = $dbh->prepare("SELECT * FROM users");
$sth->execute();
$result = $sth->fetchAll();
$i=0;
foreach($result as $data) {
$final_array[$i]['id'] = $data['id'];
$final_array[$i]['userid'] = $data['userid'];
$final_array[$i]['fn'] = $data['fn'];
$final_array[$i]['ln'] = $data['ln'];
$sth2 = $dbh->prepare("SELECT location,activity FROM userdetails WHERE userid= ".$data['id']."");
$sth2->execute();
$result2 = $sth2->fetchAll();
$j=0;
$history_array = array();
foreach($result2 as $data2) {
$history_array[$j] = array("location" => $data2['location'], "activity " => $data2['activity ']);
$j++;
}
$final_array[$i]['history'] = $history_array;
$i++;
}
echo json_encode($final_array);