CODEWITHSUNDEEP
phpmysql
frosty
frosty

Reputation: 2851

MySQL select rows based on previous query

$clicks = mysqli_real_escape_string($conn,$_POST['clicks']);
$modifier = 10*$clicks;

$result = mysqli_query($conn,"SELECT * FROM posts ORDER BY freshness LIMIT $modifier,10");

while($row = mysqli_fetch_assoc($result)){
    $posts[] = $row;
}

$result2 = mysqli_query($conn,"SELECT * FROM comments ORDER BY time WHERE ????");

while($row2 = mysqli_fetch_assoc($result2)){
    $comments[] = $row2;
}

For $result2, how can I select WHERE of each of the 10 post IDs (i.e. $row['id']) from $result? Otherwise I'd be selecting every single comment in the table unnecessarily.

Upvotes: 2

Views: 785

Answers (2)

Markus AO
Markus AO

Reputation: 4889

You could for example do the following, using your current code and an IN clause for MySQL.

while($row = mysqli_fetch_assoc($result)){
    // Here we use the ID for the array key.
    $posts[$row['id']] = $row;
}

// Here we get the array keys and implode the IDs into a comma-separated list.

$row_ids = implode(',', array_keys($posts));

// And here we plug it into an `IN` statement.

$result2 = mysqli_query($conn,"SELECT * FROM comments WHERE post_id IN ({$row_ids}) ORDER BY `time`");

// May want to change the 'time' above into a non-reserved word...

while($row2 = mysqli_fetch_assoc($result2)){
    // Here we add the comments into arrays associated by post_id.
    $comments[$row2['post_id']][] = $row2;
}

Yes I realize that crafting a JOIN instead would look sexy. It could also accrue a lot of overhead. Now benchmarking the scenarios... For which I've created sample tables for posts (1000 posts) and comments (5000 comments with a random post's ID) for benchmarking. (Index on the timestamps.)

Case 1: First selecting 50 posts ordered by date. Then selecting all their comments using IN with post IDs as above, again ordered by date. (Returns 50 posts and 268 comments on each loop with this test data.) Result: 1000 loops in 3.0888 sec (avg. 0.0030888 sec).

Case 2: Using the JOIN example by @OllyTeneriffe. (I set LIMIT to 268 to match Case 1's data volume, "cheating" on the limit clause problem noted in the comments.) Results fetched in a single loop. Result: 1000 loops in 25.7868 sec (avg. 0.0257868 sec).

Repeated tests with LIMIT 200 (yielding 200 posts + 971 comments). Case 1: 8.2992 sec (avg. 0.0082992 sec). Case 2: 33.5089 sec (avg. 0.0335089). The results are not as sexy as the join may look. In this case, the "simple" approach is an order of magnitude faster.

Upvotes: 1

OllyBarca
OllyBarca

Reputation: 1531

Why not do this all in a single query?

 $modifier = 10*$clicks;

 $query = "SELECT *
    FROM posts 
    INNER JOIN comments
    ON posts.id = comments.post_id
    ORDER BY posts.freshness 
    LIMIT '$modifier'";

Upvotes: 3

Related Questions