如何单独显示select查询结果？

Question

$data = mysql_query(" SELECT * FROM user_pokemon_db WHERE user_id = '".$battleid."' AND team != '0' ORDER BY team") or die(mysql_error());
while($rows = mysql_fetch_assoc($data))
{
$battlepkmn_id = $rows['pkmn_id'];
$battlepath = mysql_query(" SELECT * FROM pokemons WHERE pk_id = '".$battlepkmn_id."' ");
$battlepoke = mysql_result($battlepath, 0, "path");
echo $battlepoke;
$battlepokeid = $rows['id']; 
$battlelevel = $rows['level'];
echo $battlelevel; 
$battlehealth = $rows['health']; 
echo $battlehealth;
$battleexp = $rows['exp'];
}

我不想在同一个while循环中使用echo语句。我该怎么办……？我试过below..but不起作用！？

echo $battlepoke[0];
echo $battlelevel[0]; 
echo $battlehealth[0];

Answer 1

//Prepared Statement
$stmt = $db->prepare("SELECT * FROM user_pokemon_db 
WHERE user_id = ? 
AND team != '0' 
ORDER BY team");

//Prepared execution - I chose string but if it is an integer for $battleid just change 's' to 'i'
$stmt->bind_param('s', $battleid);

//Execution of prepared statement
$stmt->execute();

//Assign to result
$result = $stmt->get_result();

//Loop through all returned rows for the above query
foreach ($result as $row) {

//Grab first row
$row = $result->fetch_assoc();

//Assign variable name for the pkmn_id for first row
$battlepkmn_id = $row['pkmn_id'];


//Prepared Statement
$stmt = $db->prepare("SELECT * FROM pokemons 
WHERE pk_id = ?");

//Prepared execution - I chose string but if it is an integer for $battleid just change 's' to 'i'
$stmt->bind_param('s', $battlepkmn_id);

//Execution of prepared statement
$stmt->execute();

//Assign to result2
$result2 = $stmt->get_result();

//Assign results to row2 for above query
$row2 = $result2->fetch_assoc();

//Assign results to variable names
$battlepoke = $row2['path'];
$battlepokeid = $row2['id']; 
$battlelevel = $row2['level'];
$battlehealth = $row2['health']; 
$battleexp = $row2['exp'];

//Display the info to user
echo $battlepoke;
echo "<br />";
echo $battlelevel; 
echo "<br />";
echo $battlehealth;
echo "<br />";
echo "<br />";
}

Answer 2

尝试仅使用包含两个表的SELECT，请参阅：

SELECT
    user_pokemon_db.id AS ID,
    user_pokemon_db.level AS LEVEL,
    user_pokemon_db.helth AS HELTH,
    user_pokemon_db.exp AS EXP,
    user_pokemon_db.user_id AS USER_ID,
    user_pokemon_db.team AS TEAM,
    user_pokemon_db.pkmn_id AS USER_PK_ID,
    pokemons.path AS BATTLE_POKE
FROM
    user_pokemon_db,
    pokemons
WHERE
    user_pokemon_db.team != '0' AND
    user_pokemon_db.user_id = ? AND
    user_pokemon_db.pkmn_id = pokemons.pk_id
ORDER BY team

注意:使用mysqli_* (或new mysqli)代替mysql_*

快速示例：

if(is_numeric($battleid)) {//Prevent injection
    $query = 'SELECT
            user_pokemon_db.id AS ID,
            user_pokemon_db.level AS LEVEL,
            user_pokemon_db.helth AS HELTH,
            user_pokemon_db.exp AS EXP,
            user_pokemon_db.user_id AS USER_ID,
            user_pokemon_db.team AS TEAM,
            user_pokemon_db.pkmn_id AS USER_PK_ID,
            pokemons.path AS BATTLE_POKE
        FROM
            user_pokemon_db, pokemons
        WHERE
            user_pokemon_db.team != '0' AND
            user_pokemon_db.user_id = ' . $battleid . ' AND
            user_pokemon_db.pkmn_id = pokemons.pk_id
        ORDER BY team';

    if ($data = mysqli_query($link, $query, MYSQLI_USE_RESULT)) {
       while ($result = mysqli_fetch_array($data, MYSQLI_ASSOC)) {
          var_dump($result); //print data
       }
    }
} else {
   echo 'Invalid ID'; 
}

示例返回(控制台)：

ID | LEVEL | HELTH | EXP | USER_ID | TEAM | USER_PK_ID | BATTLE_POKE
-----------------------------------------------------------------------
1  |   2   |   80  | 250 |   5     |   8  |    151     |     path