In this activity you will use PHP to connect to the database and run a query.

• Create a new file called 'mysql.php' in your top level directory in Visual Studio Code.
• Add the base PHP code to include the header, menu and footer.

<?php
    // begin HTML code
    include('header.php');
    include('menu.php');
?>

<?php
    // finish HTML code
    include('footer.php');
?>

• While there are a number of ways to connect to databases in PHP, we'll be using PDO for its greater security.
• Copy the following code at the top of the first PHP block.

    // Set up database connection
    define ('DBCONNECT', "../../pdo.php"); // database connection
    include_once DBCONNECT;
    $db  = 'webdev';
    $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());
    }

• As described in the comment, this code will set up the database connection. Let's take a look at how.
• The first line (define ('DBCONNECT', "../../pdo.php");) defines a static (unchangeable variable) called DBCONNECT that holds the relative address of a file outside the web server. This file holds our host, username and password for the database as well as the options we'll use to connect. We don't want these available on the server because if PHP fails for any reason, our full code will be exposed to users and a malicious person could use this information to access our database and ruin our site or use our server for illegal activities. By storing the data outside of the web server, the user can never see this detail.
• This data is accessed by including the external file. include DBCONNECT;
• In the next line we define the database name ($db = 'webdev';) because each application / site could use a different database so we need to define it inside the application / site.
• The fourth line creates a string with all our connection details including the host, user name and the character set we'll use. $dsn = "mysql:host=$db_host;dbname=$db;charset='utf8mb4'";
• The final block sets up the connection. This primarily occurs in $pdo = new MyPDO($dsn, $db_user, $db_pass, $db_options);, where $pdo is an object (a more advanced type of variable) that includes both the database connection and the functions needed to access the data.
• We wrap the connection in a try / catch block to handle any errors that may occur if the connection fails. In such a case, our code will throw an exception that includes a message and the error code.
• It's not necessary to remember all this. It's sufficient to know where to find it and to include it in your code each for each site.

• PDO keeps our database safe from direct injection hacks by separating out the query from the data.
• Add the following code at the bottom of the first PHP block, after include('menu.php');.

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

• As you can see, the first line is the query we created in the previous activity, and the second line creates an array that will contain any data we wish to include as part of the query.
• Next, we need to set up the query by sending only that data to the database.

    $rslt  = $pdo->prepare($query);

• $rslt is now a variable that holds the result of our query.
• We can now safely send our parameters and have the database run our query.

    $rslt->execute($args);

• The final step is to retrieve the results of the query, which we do in a loop.

• Just as in Javascript, PHP has many types of loops. The next we'll look at is the 'while loop'.
• Add the following code inside a PHP block. It might be best to put it at the end to keep it separate. Change my name to yours so you can find your results in the logs.

    $i = 0;
    while ($i < 10) {
        error_log("MURRAY: the value of i is $i");
        $i++;
    }

• $i = 0; defines a variable and sets it to '0'.
• while ($i < 10) {} runs the code inside {} as long as $i is less than 10.
• error_log("MURRAY: the value of i is $i"); outputs a string containing the value of $i to the logs so we can see it.
• Finally, $i++; increments $i each loop so that it doesn't go on forever.
• Open the page 'mysql.php' and check your logs using Putty.

cat /var/log/apache2/error.log | grep 'MURRAY'

• You should see your text ten times with the number increasing from 0 to 9.
• So this is another way of running the same code multiple times.
• Let's use it to get our data line by line.
• Add the following code after $rslt->execute($args);

    while ($row = $rslt->fetch()) {

    }

• This code fetches the a single row of data from our query result $rslt and stores it in the variable $row.

• Let's display that row in our error log. Place the following code inside the {}.

        error_log("MURRAY: query response row - " . print_r($row,1));

• If you refresh your page and check your logs, you'll see the data from our database.
• Notice the new command print_r. This is more flexible than echo, which can't print arrays or objects.
• The first parameter $row is the content to be displayed.
• The second parameter 1 tells print_r to convert the first parameter to a string, which can then be displayed using echo or error_log.
• Let's display this data in HTML. Add the following code between the PHP blocks.

    <main>
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Hero / Villain</th>
                    <th>First Appearance</th>
                    <th>Power</th>
                    <th>Movie</th>
                </tr>
            </thead>
            <tbody>
                
            </body>
        </table>
    </main>

• This displays the header row of a table for our data, and prepares the table body for the data itself.
• Now, move the 'while loop' inside the table body. You'll need to include the tags to identify PHP code.

    <main>
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Hero / Villain</th>
                    <th>First Appearance</th>
                    <th>Power</th>
                    <th>Movie</th>
                </tr>
            </thead>
            <tbody>
<?php
            while ($row = $rslt->fetch()) {
                error_log("MURRAY: query response row - " . print_r($row,1));
    }
?>
            </body>
        </table>
    </main>

• There are now two primary ways to add the HTML code. One is as a PHP string. Note that the tabs and new lines aren't required, but they make debugging HTML easier.

<?php
    while ($row = $rslt->fetch()) {
        error_log("MURRAY: query response row - " . print_r($row,1));
        echo "\t\t\t\t<tr>\n";
        echo "\t\t\t\t\t<td>$row['character_name']</td>\n";
        echo "\t\t\t\t\t<td>$row['hero_villain']</td>\n";
        echo "\t\t\t\t\t<td>$row['first_appeared']</td>\n";
        echo "\t\t\t\t\t<td>$row['power']</td>\n";
        echo "\t\t\t\t\t<td>$row['movie']</td>\n";
        echo "\t\t\t\t</tr>\n";
    }
?>

• The other is to add the HTML code, then insert our data individually.

<?php
    while ($row = $rslt->fetch()) {
?>
                <tr>
                    <td><?php echo $row['character_name'];?></td>
                    <td><?php echo $row['hero_villain'];?></td>
                    <td><?php echo $row['first_appeared'];?></td>
                    <td><?php echo $row['power'];?></td>
                    <td><?php echo $row['movie'];?></td>
                </tr>
<?php
    }
?>

• Choose whichever you like. I prefer the second as it's a lot easier to read.
• Note that we need to surround each segment of PHP code in the PHP tags. Be careful with this as it's where you'll most likely make a mistake.

• While what we've done is great, it feels like we're reprinting data unnecessarily.
• It would be better to present one line for each hero with a list of movies they've appeared in.
• To do that, let's repackage the data into another array.
• Comment out (or delete) the existing code inside the while loop.
• Create some space inside the while loop and add the following code to the first line.

        $hero_data[$row['character_name']]['hero_villain']   = $row['hero_villain'];

• Here we are creating an array called $hero_data for all the heroes and inside that, we are creating an array for each character ($hero_data[$row['character_name']]).
• The character array has an element called 'hero_villain' with the value extracted from the current row of data.
• Notice that in PHP we can name an array element. This isn't possible in Javascript, where array elements are always numbered.
• The trick is that if the character is repeated, it will overwrite the existing data and we don't mind. That is, $hero_data['Black Widow']['hero_villain'] will be 'Hero' every time, so we don't mind if it's overwritten. * Do the same for 'first_appeared' and 'power'. <code> $hero_data[$row['character_name']]['first_appeared'] = $row['first_appeared']; $hero_data[$row['character_name']]['power'] = $row['power'];</code> * We don't want to overwrite previous movies as subsequent rows are processed for the same character, so we need to do something a bit more complicated. We need to add each movie to the end of the list. <code> $hero_data[$row['character_name']]['movies'] .= $row['movie'] . "<br>";</code> * The <html><br> added after each movie will move each to its own line in the table cell so it's easier for the user to read.

• There's another loop that is extremely useful for processing arrays. It's the 'foreach' loop.
• Add the following code to the end of the last block of PHP code.

    $my_array = array('item1' => 'dog', 'item2' => 'elephant', 'item3' => 'rabbit');
    foreach($my_array as $name => $item) {
        error_log("MURRAY: $name is a $item");
    }

• The first line creates an array, but unlike the arrays we've seen, each item in the array is named.
• Instead of referring to an item by number ($my_array[0]) we can refer to each item by name ($my_array['item2']).
• The second line sets up the loop. It says 'run through this code for each item in the array $my_array, where the reference for each item is $name and the item itself is $item'.
• We can now refer to the name and the actual item by using the variables $name and $item.
• error_log then reproduces these values so that we can read them in the error log.
• Try it now to be sure you understand it.
• The value of this is that we don't need any extra code to determine the length of the array.
• Now, to display our hero data, we can loop through the $hero_data array.

    foreach($hero_data as $name => $details) {

    }

• Because $hero_data is a nested array (an array within an array), $details itself is an array.
• We can now generate our HTML code using $name and the $details array.

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

• Run this code to see that it shows a single row per hero and lists all the movies (all those captured in the database) in the last cell.

Next: Exercises