可以在 while 循环内查询吗? [英] Is it ok to query inside a while loop?

问题描述

我在一个数据库中有两个表.我查询第一个表限制 10 然后循环结果.在 while 循环中，我使用第一个查询中的数据作为参数再次执行另一个查询.这是脚本的示例:

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);
    }
?>

所以如果我有 100 行的结果，那么第二个查询将执行 100 次.我知道这不好.我怎样才能让它更好.有没有办法在一个查询中做所有事情?如果在 while 循环中还有另一个查询，其中使用第二个查询中的数据作为参数怎么办?

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.:我正在做一个在线游戏的排名系统.

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

推荐答案

如果使用 JOIN，则可以在一个查询中完成.

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

您也可以使用嵌套查询

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

你也可以把所有的game_character.id放到一个数组中，使用

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) . ")";

这篇关于可以在 while 循环内查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！