CODEWITHSUNDEEP
phpmysql
Jhon Andrew
Jhon Andrew

Reputation: 188

Is it ok to query inside a while loop?

I have two tables in one database. I am querying the first table limit by 10 then loop the results. And inside the while loop, I am doing again another query using a data from the first query as a parameter. Here is an example of the script:

<?php

    $con = mysql_connect(host,username,password);
    mysql_select_db(game_server);

    //My first query
    $q1 = mysql_query('SELECT * FROM game_characters ORDER BY score DESC LIMIT 10');

    while($character = mysql_fetch_object($q1)){
    //My second query
        $q2 = mysql_query('SELECT * FROM game_board WHERE id="'.$character->id.'"');
        $player = mysql_fetch_object($q2);
    }
?>

So if I have a result of 100 rows, then the second query will execute 100 times. And I know it is not good. How can I make it better. Is there a way to do everything in one query? What if there is another query inside the while loop where a data from the second query as a parameter is used?

P.S.: I am doing a rankings system for an online game.

Upvotes: 2

Views: 8295

Answers (4)

German Rumm
German Rumm

Reputation: 5832

You can do it in one query if you use JOINs.

SELECT * FROM game_board AS b 
    LEFT JOIN game_characters AS c ON b.id = c.id 
ORDER BY c.score DESC 
LIMIT 10

You can also use nested query

SELECT * FROM game_board AS b WHERE 
  id IN (SELECT id FROM game_characters AS c ORDER BY score DESC LIMIT 10)

You can also put all game_character.id into an array, and use

$sql = "SELECT * FROM game_board AS b WHERE b.id IN (" . implode(', ', $game_character_ids) . ")";

Upvotes: 4

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

What about if you do something like the following: 

<?php

    $con = mysql_connect(host,username,password);
    mysql_select_db(game_server);

    //My first query
    $q1 = mysql_query('SELECT * FROM game_characters ORDER BY score DESC LIMIT 10');

    while($character = mysql_fetch_object($q1)){
    //My second query
        $characters .= " ' $character->id ' ,"
    }
    $q2 = mysql_query("SELECT * FROM game_board WHERE id in (substr($characters,0,strlen($characters - 2))");
    $player = mysql_fetch_object($q2);
?>

Upvotes: -1

seekme_94
seekme_94

Reputation: 162

A better approach here would be to collect all the IDs in a concatenated string str in form 'id1', 'id2', 'id3', ... and use: select * from game_board where id in (str)

Upvotes: 1

shadyyx
shadyyx

Reputation: 16065

Why not using JOIN?

This way there will be no queries within the while loop:

$con = mysql_connect(host,username,password);
mysql_select_db(game_server);

//My first query
$q1 = mysql_query('
    SELECT * 
    FROM game_characters gc 
    LEFT JOIN game_board gb ON gc.id = gb.id
    ORDER BY score DESC 
    LIMIT 10
');

while($character = mysql_fetch_object($q1)){
    // do Your stuff here, no other query...

}

Upvotes: 1

Related Questions