MySQL select rows based on previous query

Question

$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.

How about using a join and just getting everything in one query? — Gordon Linoff
– Gordon Linoff, Commented Jun 7, 2015 at 22:56
Could you explain further what that is? I'm new to PHP/MySQL — frosty
– frosty, Commented Jun 7, 2015 at 23:02
@Mureinik posts has columns id plus a whole bunch of other columns which I'm assuming are irrelevant for this; comments also has id as well as post_id which is the same as id in posts (they're linked) — frosty
– frosty, Commented Jun 7, 2015 at 23:05

OllyBarca · Accepted Answer · 2015-06-07 23:48:02Z

3

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'";

answered Jun 7, 2015 at 23:48

OllyBarca

1,5391 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

frosty Over a year ago

I think this is only selecting one comment per post, rather than all the comments for each post.

Markus AO Over a year ago

It does give you all the comments. However, since the rows are joined, you will have rows with fields like: post:1+comment:1, post:1+comment:2, etc. and the LIMIT clause here will not limit the number of posts, as you might expect, but rather it would chop whenever LIMIT amount of comments are matched. Suppose your first post has 100 comments and you want to fetch 50 posts using LIMIT. With this query, you will not even get all the comments to your first post, much less the remaining posts.

Markus AO · Accepted Answer · 2015-06-08 02:09:22Z

1

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.

edited Jun 8, 2015 at 2:09

answered Jun 7, 2015 at 23:14

Markus AO

4,8892 gold badges20 silver badges29 bronze badges

10 Comments

Markus AO Over a year ago

You will also want to have the ORDER BY after the WHERE... Edited above.

frosty Over a year ago

This is giving me an error mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean starting here while($row2 = mysqli_fetch_assoc($result2)){ $comments[] = $row2; }

user557846 Over a year ago

i don't know why any one would upvote this its a far inferior option to using a join.

Markus AO Over a year ago

@frosty I am right now benchmarking an almost-identical query, works fine. You may want to change time into something else or put it inside backticks, since it's a reserved MySQL keyword. Otherwise, use mysqli_error() please, so you can see where things go wrong. @Dagon and what about performance-wise? Care to sketch in a sample join that doesn't accrue crazy over-head if he wants to SELECT * and needs to e.g. join dozens of comments per each post?

frosty Over a year ago

I got it to work, it was the most silly error because I was converting some things to simpler terms for the example here.

|

Collectives™ on Stack Overflow

MySQL select rows based on previous query

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related