Web Development Lesson 7 - Databases

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');
?>

• Objects are an advanced data structure and we won't go into it too much here, but you'll need to understand a little bit to use the PHP database connection in this actviity.
• An object is like a named array where you can access each element by name.
• But an object also has functions built into. Recall that in Javascript, audio has built-in functions like .play(), .paused() and built-in information like .paused. That's because audio is an object.
• Similarly, in PHP, an object can have both data and functions. For example, a database connection object can have information about the connection and also have functions to extract data.
• Whereas you access the information in an array using [] , we access information and functions using ->.
• To use an abstract example, we could create an object called $table.
• We could set the number of legs on the table as follows.

$table->legs = 4;

• We could also move the table to another location as follows.

$table->move($location);

• There is a whole school of programming based on objects (Object-oriented programming) and objects could be the subject of many classes, but this is all you need to know to use the database connection.

• 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', "/home/dh_9m7wr9/pdo.php"); // database connection
    include_once 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());
    }

• 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 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 an object 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 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 use of two parameters with print_r.
• 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));
    }
?>
            </tbody>
        </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 (first published etc) 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.
• 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'.

        $hero_data[$row['character_name']]['first_appeared'] = $row['first_appeared'];
        $hero_data[$row['character_name']]['power']          = $row['power'];

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

        $hero_data[$row['character_name']]['movies']        .= $row['movie'] . "<br>";

• The <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.
• 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 table row per hero and lists all the movies (all those captured in the database) in the last cell.
• Here's the final code in case you are having trouble with placement of the various items.

<?php
    // Set up database connection
	define ('DBCONNECT', "../../pdo.php"); // database connection
    include 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());
    }

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

    // extract data
    $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()) {
        $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
    // finish HTML code
    include('footer.php');

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

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

Next: Exercises