Multiple queries and then sort those results in order to appear in order by date

Question

I need to do N consultations according to a value received from another consultation.

$idList = array();
if (@$_GET["page"] == "profile"){
    $idList[0] = $_GET['pid'];
    $cont = 0;
}
else {
    $idList[0] = $uid;
    $cont = 1;
    $query = mysqli_query($conn, "SELECT * FROM `friends_list` WHERE user_id = '$uid'");
    while($friends = mysqli_fetch_array($query)){
        $idList[$cont] = $friends['friend_id'];
        $cont++;
    }

    $cont = count($idList);
    $cont--;
}


$pos = 1;
while($cont>= 0){
    $id = $idList[$cont];
    $query2 = mysqli_query($conn,"SELECT * FROM `post` WHERE user_id = $id'");
    while($posts = mysqli_fetch_assoc($query2)){
        $pList[$pos] = $posts; 
        $pos++;
    }
    $cont--;
}
$cont = 1;

foreach($pList as $post){

    $id = $post['user_id']; 
    $query3 = mysqli_query($conn,"SELECT * FROM `profile` WHERE id = '$id' LIMIT 1") or die(mysqli_error($conn));
    $owner = mysqli_fetch_assoc($query3);

In this case, I’ve stored the results in a vector, but I’m not sure that’s the best way to do it. Detail, I have no idea how to organize the vector effectively because the amount of results can be very large.

What is the best way to do this search? Through vector or there is another way to get and organize the data?

And if it is vector, any suggestions of how to organize by date?

Thank you.

Answer 1

You can’t minimize the use of so many ties by joining all the querys into one?

For example: (sql updated to list user posts as well)

"SELECT * FROM `friend_list` AS `fl`
INNER JOIN `post` AS `p` ON `p`.`user_id` = `fl`.`friend_id` or `p`.`user_id` = `fl`.`user_id`
INNER JOIN `profile` AS `prof` ON `prof`.`id` = `p`.`user_id`
WHERE `fl`.`user_id` = '{$id}'
ORDER BY `p`.`data` DESC"

It’s okay for you to join and concatenate everything within a vector, just be careful about performance if you have many items to list.

The most correct thing, if there are many items, is to create a pagination to only display "x" items per page (some frameworks make this very easy). Or, as in a Timeline, load only "x" items as the user scrolls down the page and does not load all at once.

If you choose to do everything in this single query (with Inner joins), be careful with the name of the columns in each table, if there are repeated names may cause some unexpected behavior.

If you wanted to continue using the same method of multiple loops, I believe that an order by in the post table already solves your problem:

$query2 = mysqli_query($conn,"SELECT * FROM `post` WHERE user_id = $id' ORDER BY `data` desc");

Another way to organize the vector would also be using the "array_map" native to PHP.