0

enter image description here

i have created two tables. one episode table and one season table and i wanna know how can i select and display episodes counts for each season or save them in season.episodes_count what is the right way or query for doing this?

enter image description here

try {
    $stmt = $db->prepare("SELECT * FROM `season` WHERE season.show_id = '" . $show_id . "' ORDER BY season_number DESC");
    // how to select episodes_count?
    $stmt->execute();
    $seasons = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo $e;
}
Ramiel
  • 33
  • 1
  • 8
  • 4
    Remove `episodes_count` from table `season`. Add `season_id` into table `episode` instead. Then `JOIN` both tables using `COUNT()` – B001ᛦ Apr 29 '21 at 13:40
  • 2
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Apr 29 '21 at 13:41
  • @B001ᛦ you mean left join `episode` on `season` AS `episodes_count`? – Ramiel Apr 29 '21 at 14:11
  • @B001ᛦ how can i group episodes by season when i don't have `season_id` in show page and looped them and displaying them? – Ramiel Apr 29 '21 at 14:18

1 Answers1

0

Hi don't have your DB to test against and this is not tested but somehitng like this:

    $stmt = $db->prepare("
    SELECT se.id,
    se.season_number,
    COUNT(es.id) AS episodes_count
    FROM season AS se
    LEFT JOIN espisode AS es ON (se.id = es.season_id)
    WHERE se.show_id = $show_id
    ORDER BY se.season_number DESC
    ");
pl38
  • 36
  • 2