Query within a while loop

Question

I have this query call:

$currplayer = 1;

$query = "SELECT f.fk_user_id2, u.username, u.profileimg FROM ".$prefix."_friends f 
                        INNER JOIN ".$prefix."_users u
                            ON f.fk_user_id2 = u.id     
                    WHERE f.fk_user_id1 = $currplayer ORDER BY u.username ASC";

if ($result = $mysqli->query($query)){
    while ($row1 = $result->fetch_assoc()){

        $friendid = $row1['fk_user_id2'];

        $sql = $mysqli->query("SELECT * FROM ".$prefix."_newversus WHERE fk_player=$currplayer AND fk_opponent=$friendid");
        $row = $sql->fetch_assoc();

I am trying to figure out how I get the query within the loop into the first query with inner join, but can't figure this out.

hoping for help and thanks in advance :-)

Sumoanand · Accepted Answer · 2013-05-30 19:27:53Z

1

If i got you corrrectly, you want to remove the query which is running in loop & add it to main query.

Try this:

SELECT f.fk_user_id2, u.username, u.profileimg FROM ".$prefix."_friends f 
INNER JOIN ".$prefix."_users u ON f.fk_user_id2 = u.id 
INNER JOIN ".$prefix."_newversus n ON f.fk_user_id2 = n.fk_opponent 
WHERE f.fk_user_id1 = $currplayer AND
n.fk_player=$currplayer
ORDER BY u.username ASC;

answered May 30, 2013 at 19:27

Sumoanand

8,9293 gold badges50 silver badges46 bronze badges

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

1 Comment

Mansa Over a year ago

Almost correct, but... This will only get opponents which i have a versus against... I also want the opponents which I dont have a newversus record against?

Collectives™ on Stack Overflow

Query within a while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related