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

Asked

Viewed 62 times

0

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.

  • You want to organize the query by date, right? Why don’t you use the order by <nome_da_coluna_que_armazena_a_data ?

  • Exactly, but they are several different users_ids with multiple possibilities for each one. Ie: user_id = 1, date 27/09/2018 user_id = 1, date 25/09/2018 user_id = 3, date 1/10/2018 user_id = 4, date 30/09/2018

  • So I have to interim these user_ids ordered by date.

  • You have to sort by date, but the user_id'Do you have to stick together? That?

  • No, the ids appear in the order of the date. Style social media posts to show the posts of friends and the user himself according to the date of publication.

  • do you want to load a 'Timeline'? , with posts sorted by date? But in this case, why do you need user_ids? I think I understand, but I would like you to give some more details

  • That’s right. I need user_id to control who’s friends with who, you know? He picks up posts from friends only.

Show 2 more comments

1 answer

0


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.

  • Oops... That’s just what I needed! Thank you so much for your help! With this query, I don’t need to store in vector :) Yes, I don’t want to load everything at once. Just a little problem, on Timeline I need you to also show the user posts too. I tried to use on Where ... WHERE fl.user_id = '{$pid}' OR p.user_id = '{$pid}' ORDER BY p.date DESC"); but loads 3x each post :P

  • I created a new record for each user, where it says that he is a friend of himself. I don’t know if this is the best way, but it worked! : P

  • @Marciohrm updated the answer sql, now you should list the user posts as well. If you can, mark the answer as correct so you can help other users with the same question in the future :)

  • Oops, it actually worked! The only problem is that it is returning 3x the same publication as the logged-in user. I’ll try to figure out how to fix it, if it doesn’t work out, I’ll leave it as it was before you change the code! ! (:

Browser other questions tagged

You are not signed in. Login or sign up in order to post.