循环运行多个查询，并构建多维数组 [英] Running multiple queries in loop, and building multidimensional array
问题描述
我正在根据用户关注的用户构建数据流.虽然我有一个工作原型，但代码看起来不必要地丑陋，其中包含多个循环以及嵌套在其中的进一步查询.我想知道我是否可以得到关于简化这个的任何建议，可能在 sql 查询本身中处理更多?使用一些代码:(我已将其精简并删除了一些检索到的行).
I'm building a stream of data based on users a user is following. While I have a working prototype, the code seems unnecessarily ugly with multiple loops with further queries nested in them. I was wondering if I could get any advice on simplifying this, possible handling more within the sql query itself? On with some code: (I've trimmed this down a little and removed some of the rows retrieved).
$init = $conn->prepare("SELECT followerid FROM following WHERE userid=?");
$init->bind_param("s", $userid);
$init->execute();
$init->bind_result($idq);
$init->store_result();
$num = $init->num_rows();
while($init->fetch()) {
// get all to be done by each user
$stmt = $conn->prepare("SELECT activityId FROM done WHERE userId=? ORDER BY number DESC");
$stmt->bind_param("s", $idq);
$stmt->execute();
$stmt->bind_result($aiddo);
$stmt->store_result();
$num_do = $stmt->num_rows;
while($stmt->fetch()) {
$activityId_do[] = $aiddo;
}
// get location information
for($i=0; $i<=$num_do; $i++) {
$act_done = $conn->prepare("SELECT fullAddress FROM `activity` WHERE (id=?)");
$act_done->bind_param("s",$activityId_do[$i]);
$act_done->execute();
$act_done->bind_result($fullAddress);
while($act_done->fetch()) {
$do_array[] = array(
"fullAddress"=>$fullAddress,
"type"=>"do"
);
}
}
//get all done by each user
$stmt = $conn->prepare("SELECT activityId FROM done WHERE userId=? ORDER BY number DESC");
$stmt->bind_param("s", $idq);
$stmt->execute();
$stmt->bind_result($aid);
$stmt->store_result();
$num_done = $stmt->num_rows;
while($stmt->fetch()) {
$activityId[] = $aid;
}
for($i=0; $i<=$num_done; $i++) {
$act_done = $conn->prepare("SELECT fullAddress FROM `activity` WHERE (id=?)");
$act_done->bind_param("s",$activityId[$i]);
$act_done->execute();
$act_done->bind_result($fullAddress);
while($act_done->fetch()) {
$done_array[] = array(
"fullAddress"=>$fullAddress,
"type"=>"done"
);
}
}
//get all stories by each user
$stmt = $conn->prepare("SELECT activityId FROM story WHERE userId=? ORDER BY number DESC");
$stmt->bind_param("s", $idq);
$stmt->execute();
$stmt->bind_result($aidst);
$stmt->store_result();
$num_story = $stmt->num_rows;
while($stmt->fetch()) {
$activityId_story[] = $aidst;
}
for($i=0; $i<=$num_story; $i++) {
$act_done = $conn->prepare("SELECT fullAddress FROM `activity` WHERE (id=?)");
$act_done->bind_param("s",$activityId_story[$i]);
$act_done->execute();
$act_done->bind_result($fullAddress);
while($act_done->fetch()) {
$story_array[] = array(
"fullAddress"=>$fullAddress,
"type"=>"story"
);
}
}
}
我最初想在初始 while 循环中的三个查询上使用联合，但是从查询构建的每个数组都必须显示type"=>"字段，这非常重要，因为项目在流依赖于此.
I originally thought about using a union on the three queries inside the initial while loop, but each array built from the query MUST display the "type"=>"" field, this is hugely important as the way items are displayed in the stream depend on this.
导致我相信必须有一种打击方式的主要原因之一，因为在第二个 while 循环中嵌套查询会引发一系列错误，因此在行数上使用 for 循环.但是这段代码就是感觉不对.感觉很丑，而且真的很重复，虽然有效，但我觉得不应该解决.
One of the main reasons I was lead to believe there must be a batter way as nesting a query in the second while loop kicked up a stink of errors, thus the use of the for loop on the amount of of rows. But this code just doesn't feel right. It feels ugly and really repetitive, and although it works, I feel it shouldn't be settled for.
在运行这段代码后，所有 3 个数组都合并为一个多维数组，如下所示:
After this block of code is run, all 3 arrays are merged into one multidimensional array, like so:
Array
(
[0] => Array
(
[fullAddress] => London, England
[type] => do
)
[1] => Array
(
[fullAddress] => Portsmouth, England
[type] => done
)
[2] => Array
(
[fullAddress] => Paris, France
[type] => story
)
)
有没有更好的方法来做到这一点，这是在这种情况下构建多维数组的最佳方法吗?
Is there a better way to do this and is this the best way to build the multi-dimensional array in this case?
推荐答案
我会在数据库中为 followed_users 或其他内容创建一个表，您可以在其中将您的用户链接到他们关注的用户的 ID，然后查询该表以获取 id 列表并运行下一个查询以获取要提取的信息(或者，如果数据很小，也可以将该数据存储到表中
I would create a table in the database for followed_users or something, where you link your user to the id's of users they are following, then you query that table for the list of id's and run your next query to get the information you are pulling (or, if the data is small, store that data as well into the table
=======================================
| id | user_id | following_id | type |
=======================================
| 1 | 101 | 12 | story |
| 2 | 108 | 15 | story |
| 3 | 108 | 16 | do |
| 4 | 108 | 19 | done |
=======================================
甚至可以添加地址，如果这就是您要拉的全部
maybe even add in the address, if that is all you are pulling
这篇关于循环运行多个查询，并构建多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
