php - 使用 union all 或 single 语句的准备好的语句

Question

我应该使用哪个？在第二个中，有未知数量的陈述。我对准备好的陈述知之甚少。试图从简单的查询更改我的代码。我被困在这里，因为在我看来，如果我使用 top one，我将不得不提出很多请求，而且速度会更慢。

$stmt  = $con->prepare("SELECT * FROM dbtable WHERE col1=? AND col2=?");

或

$stmt  = $con->prepare("
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=? UNION ALL
    SELECT * FROM dbtable WHERE col1=? AND col2=?
");

编辑:

我的实际代码是:

foreach ($games as $ob)
{
    $sql.= "SELECT * FROM (SELECT gamesdata.rating, gamesdata.name, games.game, games.game_id FROM gamesdata 
        INNER JOIN login ON gamesdata.user_id=login.user_id 
        INNER JOIN games ON games.game_id=gamesdata.game_id 
        WHERE username='".$user."' AND game='".$ob->game."' AND gamespl>0 ORDER BY rating DESC LIMIT 5) DUMMY_ALIAS".$i." UNION ALL ";
    $i++;
}

尝试将其更改为准备好的语句。

Answer 1

您想设置准备好的语句一次，然后使用每个更改的输入运行它。

$stmt = $con->prepare("SELECT * FROM dbtable WHERE col1=:user AND col2=:game UNION ALL");

$prepared = array('user' => $user); // user doesn't change
foreach ($games as $ob) { // for each game
   $prepared['game'] = $ob->game; // set this queries game
   $stmt->execute($prepared); // execute with current game
   while ($row = $stmt->fetch()) { // iterate over rows
       var_export($row);
   }
}