<?php
    // Configurar la conexión a la base de datos
    define ('DBCONNECT', "../../../../../pdo.php"); // conexión a la base de datos
    include DBCONNECT;
    $db  = 'techschoolwebdev';
    $dsn = "mysql:host=$db_host;dbname=$db;charset=utf8mb4";
    try 
    { // connect
        $pdo = new MyPDO($dsn, $db_user, $db_pass, $db_options);
    } catch (\PDOException $e) {
        throw new \PDOException ($e->getMessage(), (int)$e->getCode());
    }

    // comienza el código HTML
    include('cabecera.php');
    include('menu.php');

    // extraer datos
    $query = "SELECT heroes.character_name, hero_villain, first_appeared, power, movie
    FROM heroes
        LEFT JOIN appearances ON heroes.character_name=appearances.character_name";
    $args = array();
    $rslt  = $pdo->prepare($query);
    $rslt->execute($args);
?>
    <main>
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Hero / Villain</th>
                    <th>First Appearance</th>
                    <th>Power</th>
                    <th>Movies</th>
                </tr>
            </thead>
            <tbody>
<?php
    while ($row = $rslt->fetch()) {
        // error_log("MURRAY: query response row - " . print_r($row, 1));
        $hero_data[$row['character_name']]['hero_villain']   = $row['hero_villain'];
        $hero_data[$row['character_name']]['first_appeared'] = $row['first_appeared'];
        $hero_data[$row['character_name']]['power']          = $row['power'];
        $hero_data[$row['character_name']]['movies']        .= $row['movie'] . "<br>";
    }
    foreach($hero_data as $name => $details) {
?>
                <tr>
                    <td><?php echo $name;?></td>
                    <td><?php echo $details['hero_villain'];?></td>
                    <td><?php echo $details['first_appeared'];?></td>
                    <td><?php echo $details['power'];?></td>
                    <td><?php echo $details['movies'];?></td>
                </tr>
<?php
    }
?>
            </body>
        </table>
    </main>
<?php
    // terminar código HTML
    include('footer.php');
?>