Mysql Join two tables to check if a user has made a pick

Question

Hi I am attempting to join two MySQL tables. The tables are as follows:

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

Here is the sql I've attempted to use to join the two tables:

$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;

    }
}

My intention is to check if the user has picked a winner (either home_team or away_team) from the mlb_game_feed table and if they have, I will change a link from make_pick to change_pick (with an if($count) statement) on the screen.

However, currently I'm not even getting any data back which means my sql is likely incorrect. Any help would be great! Thanks.

You need spaces before the `FROM`, `JOIN`, `ON`, and `WHERE clauses. — Parfait, Sep 24 '17 at 16:29
Thanks....it works...however now I see that my logic was incorrect. Currently only records that match in both tables are displayed. My intention is to continue to display all the records from the mlb_game_feed table but change a nearby hyperlink from make pick to change pick based on if a matching record is found in the user_picks table. I'm probably using the wrong type of JOIN so I am going to do some more research. — Matthew Tuman, Sep 24 '17 at 16:41
OT: Use Prepared statements with sql: https://stackoverflow.com/q/60174/5905665 — Fabian N., Sep 24 '17 at 17:03
@Strawberry ... thank you for the edit. However, you changed the OP's original setup which was a major issue in non-results per his comment. Edits should not materially change the content even if content contains issues. OP and future readers will not be aware of original issue. Can you rollback? — Parfait, Sep 24 '17 at 17:14

score 0 · Answer 1 · answered Sep 24 '17 at 17:10

Consider the following suggestions:

Use a LEFT JOIN to return ALL records and a conditional aggregate to count matches in cnt field. Later you can use this cnt to run your update hyperlink in PHP. See if block in fetch loop.
As mentioned, your SQL string that concatenates on line breaks does not leave room before the clauses of SQL: FROM, JOIN, ON, and WHERE.
Use a GROUP BY clause for your aggregate query. Non-aggregated columns must appear in this clause else it is a violation of ANSI SQL. Unfortunately, MySQL allows the ONLY_FULL_GROUP_BY mode off whereas every other RDBMS will correctly throw an error.
Use table aliases for more readable code instead of repeating long name tables.
Pass in $SESSION date as a parameter to prepared statement. See ? placeholder in string.

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...
      }

    }
}

Mysql Join two tables to check if a user has made a pick

1 Answers1