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"
);
}
}
}
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.
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.
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?
