====== Web Development Lesson 7 - Databases ======
===== PHP Database Connection =====
==== Objective ====
In this activity you will use PHP to connect to the database and run a query.
==== Setup ====
* 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.
==== Objects ====
* 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.
==== PDO 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.
==== PHP Queries ====
* 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.
| Name |
Hero / Villain |
First Appearance |
Power |
Movie |
* 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.