Multiple queries and then sort these results so that they appear in order by date

Question

Multiple queries and then sort these results so that they appear in order by date

Navigation

#1 by (0 votes)

0

I need to do N queries according to a value received from another query.

$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 the case, I stored the results in a vector, but I do not know if this is 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 is there another way to get and organize the data?

And if in case it is vector, any suggestions on how to organize by date?

Thank you.

php mysqli

asked by anonymous 01.10.2018 / 15:09

1 answer

Factoring Press using Stack Order guarantee for threads

score 0 · Accepted Answer

Can not you minimize the use of so many loops by joining all queries 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"

No problem you join and concatenate everything within a vector, just be careful about performance if you have too many items to list.

The most correct, if there are many items, is to create a page to display only "x" items per page (some frameworks make it very easy). Or, as in a timeline, just load "x" items as the user scrolls the page down and does not load everything 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 it may cause some unexpected behavior.

If I wanted to continue using the same method of multiple loops, I believe that an order by 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 of PHP.