sbourdon

Thank you, I'll look into this!

Thanks for the info; I didn't know about that! So I guess we're back at post #4! Anything wrong with that query? Thank you!

By the way, this query is being executed inside a foreach ($points as $point) { (...) } where p.userId = " . $point['userId'] . " Therefore, since we already have "p.userId", what should the query look like to get this user's missing predictions? I can get the number of missing predictions by doing the following: $missingPreds = $totalGames - $totalPreds; but I guess I could achieve the same thing in one query, right? Again, thank you for your time!

Hello and thank you for your support! Regarding my first question (which is to combine my two queries into one), here's what the new query looks like: $query = "SELECT p.userid , COUNT(*) as predictions , totgames - COUNT(*) as missing , totgames FROM " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p CROSS JOIN ( SELECT COUNT(*) as totgames FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g ) tot GROUP BY p.userid ORDER BY g.gametime DESC"; $missingPreds = " " . $this->dbo->queryForColumn($query) . ")"; Unfortunately, this returns an error 500! Is there something I haven't done correctly here? Thank you!

Hello, I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax. Here's what I would like to achieve: Table 1 includes games played since the beginning of the season (where homeTeamScore or awayTeamScore > 0): Table 2 includes predictions made by users as to which team will win each game (gameId in T2 = id in T1): The following query lets me know how many games have been played since the beginning of the season: $query = "SELECT COUNT(`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " WHERE SeasonID = " . $seasonId . " AND (homeTeamScore > 0 OR awayTeamScore > 0) ORDER BY gametime DESC"; $totalGames = $this->dbo->queryForColumn($query); And this one how many predictions each user has registered: $query = "SELECT COUNT(`g`.`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g INNER JOIN " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p ON g.id = p.gameId WHERE g.SeasonID = " . $seasonId . " AND (g.homeTeamScore > 0 OR g.awayTeamScore > 0) AND p.userId = " . $point['userId'] . " ORDER BY g.gametime DESC"; $totalPreds = $this->dbo->queryForColumn($query); First question: how could I combine these two queries into one and get the total number of missing predictions for each user? As an example, if 89 games have been played and user has registered 70 predictions, I would like to get "19" out of the query. Second question, more complex (at least to me!): what would the query look like if I wanted to know how many consecutive games has each user missed since their last prediction? As an example, if my last prediction was registered on gameId "460" and 4 more games have been played since then (id 468, 469, 470 and 471), I would like to get "4" out of the query. Hope this makes sense and is easy to understand! Thank you very much for your support!