php - Mysql 连接两个表来检查用户是否做出了选择

Question

您好，我正在尝试连接两个 MySQL 表。表格如下:

Table 1 
Name: mlb_game_feed
Fields: game_feed_game_id, date, home_team, away_team

Table 2 
Name: user_picks
Fields: pick_id, game_feed_game_id_fk, user_id_fk

这是我尝试用来连接两个表的 SQL:

$sql = "
SELECT game_feed_game_id
     , home_team
     , away_team
     , COUNT(1) as cnt
  FROM game_feed_mlb
  JOIN user_picks
    ON user_picks.game_feed_game_id_fk = game_feed_mlb.game_feed_game_id
 Where game_feed_mlb.date = '" . $_SESSION['date']."'
   AND user_picks.user_id_fk = 1";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

    // output data of each row         
    while($row = mysqli_fetch_assoc($result)) {

      $count = $row["cnt"];
      $game_id = $row["game_feed_game_id"];
      $home_team = $row['home_team'];
      $away_team = $row['away_team'];

      echo $game_id;

    }
}

我的目的是检查用户是否从 mlb_game_feed 表中选择了获胜者(home_team 或 away_team)，以及他们是否有，我会将屏幕上的链接从 make_pick 更改为 change_pick(使用 if($count) 语句)。

但是，目前我什至没有收到任何数据，这意味着我的 sql 可能不正确。任何帮助都会很棒!谢谢。

Answer 1

考虑以下建议:

1. 使用 LEFT JOIN 返回所有记录，并使用条件聚合来计算 cnt 字段中的匹配项。稍后您可以使用此 cnt 在 PHP 中运行更新超链接。请参阅获取循环中的 if block 。
2. 如上所述，在换行符上连接的 SQL 字符串不会在 SQL 子句之前留下空间:FROM、JOIN、ON ，和哪里。
3. 对聚合查询使用 GROUP BY 子句。非聚合列必须出现在该子句中，否则违反 ANSI SQL。不幸的是，MySQL 允许 ONLY_FULL_GROUP_BY模式关闭，而其他所有 RDBMS 都会正确抛出错误。
4. 使用表别名来提高代码的可读性，而不是重复长名称表。
5. 将 $SESSION 日期作为参数传递给准备好的语句。请参阅字符串中的 ? 占位符。

PHP

$sql = "SELECT g.game_feed_game_id, g.home_team, g.away_team, " .
       "       SUM(CASE WHEN g.game_feed_game_id IS NOT NULL " .
       "                THEN 1 ELSE 0 END) as cnt " .
       "FROM game_feed_mlb g " .
       "LEFT JOIN user_picks u " .
       "   ON u.game_feed_game_id_fk = g.game_feed_game_id " .
       "WHERE g.`date` = ? AND u.user_id_fk = 1 " .
       "GROUP BY g.game_feed_game_id, g.home_team, g.away_team;"

// CONFIGURE PREPARED STATEMENT AND BIND PARAMETER
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $_SESSION['date']);

// EXECUTE STATEMENT AND RETURN RESULTS
$stmt->execute();
$result = $stmt->get_result();

if (mysqli_num_rows($result) > 0) {

    // output data of each row         
    while($row = $result->fetch_assoc()) {

      $count = $row["cnt"];
      $game_id = $row["game_feed_game_id"];
      $home_team = $row['home_team'];
      $away_team = $row['away_team'];

      echo $game_id;

      if($row['cnt'] > 1) {
         // change links accordingly...
      }

    }
}