How to query result within another query

Databases
1

am trying to create a follow like system…I have the follow user id in follow_user table, am trying to get each of the follow user id and use the id to query the products table to get each of the product in that table that belong to each id…I have the script below, the problem is when new product is added it display all the product that match the company id that added the product first before it display any other product.

I want it to display each product order by the added_datetime. if one company add new product 5 days back and add another one today and between that time one company have already added their own product, it should display the new added product first and the other company product before the 5 days old added product. but now the scrpit display the 5 days old product along with it related company added prodct first…Thanks for your help in advance.

include db.php';
$timeL = $pdo->prepare('SELECT user_id FROM follow_user WHERE uid_fk = :uid_fk');
$timeL->bindParam(':uid_fk', $_SESSION['b_user_id'], PDO::PARAM_STR);
$timeL->execute();
foreach ($timeL as $rowL) {
$timeLine[] = array('user_id' => $rowL['user_id'] );
}


    if (isset($timeLine)) {
    foreach($timeLine as $fID) {
   include db.php';
    $proL = $pdo->prepare('SELECT * FROM products WHERE company_id = :id ORDER BY `added_datetime` DESC');
    $proL->bindParam(':id', $fID['user_id'], PDO::PARAM_STR);
    $proL->execute();
    foreach ($proL as $rowTL) {
    $proTimeLine[] = array('product_id' => $rowTL['product_id'],
    'product_name' => $rowTL['product_name'],
    'product_price' => $rowTL['product_price'],
    'product_description' => $rowTL['product_description'],
    'product_image_name' => $rowTL['product_image_name'],
    'company_id' => $rowTL['company_id'],
    'company_name' => $rowTL['company_name'] );
    }
    
    
    }

}
2

i read that three times and i still don’t get it

also, when posting a database problem, please show just the sql – all that php is just noise

3

@r937 thanks for your response…what am trying to do is that I want to display the list of post, just like twitter…if one post is posted the new one coming in will be on top of the old post. with my code if user A post and user B post and again user A post, the two post from user A will come on top before the post of user B…like.

user A post…
user A post…
user B post…

it should be
user A post
user B post
user A post.

4

oh, okay, now i get it :smile:

also, good thing you actually did post your php, because now i see that you’re doing this whole thing very inefficiently

never do a SELECT inside a loop produced by a previous SELECT – use a JOIN

SELECT p.company_id , p.company_name , p.product_name , p.product_price , p.product_description , p.product_image_name , p.added_datetime FROM follow_user AS f INNER JOIN products AS p ON p.company_id = f.user_id WHERE f.uid_fk = :uid_fk ORDER BY p.added_datetime DESC

2 Likes
5

THANKS @r937 …YOU ROCK :smile: …the query work fine…

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.