As you have seen so far, PHP is very capable and easy to manipulate and use to display data. Where does this data come from? There are two types of data that PHP can use: static and dynamic. We can think of static data as non-changing and dynamic as able-to-be-changed. This dynamic data is stored in a database. Simply put, a database is a structured organization of data. Think of a folder of spreadsheets. The key to databases, however, is that we can easily search or query a database based on how we have set up our structure. These queries can be as basic as “show me all the users’ first names” to “show me all the first names of users who registered on a Tuesday after 2 p.m.” The query complexity comes from the SQL in MySQL (Structured Query Language). This language, once understood, can be used with a Mad Libs approach in PHP. We can simply replace certain words and phrases with PHP variables in order to dynamically influence the result of the query.
In this chapter, we will start with the basics, of course, and simply get PHP to communicate with MySQL.
This chapter consists of the following sections:
PHP Communication with MySQL
MySQLi Advantages
PHP Connection to a Database
PHP Communication with MySQL
As you saw before, PHP needs a web server in order to run on the Web. The same is needed for MySQL. A database server (DB) is needed to run and maintain the database. In your Docker dev environment, you have this already running. With Docker, you can type docker ps at any point on your host machine (the real physical machine you are using) to see what Docker containers you have running, as shown in Figure 10-1.
Figure 10-1
Docker containers running
On the top line is an IMAGE for mysql:8.0 and on the very right side is the name mysql-db. In order for PHP to use MySQL, you need to connect to it first. PHP comes with two different methods: through the MySQLi and PDO APIs. Below are code examples of each method.
PHP Communication with the MySQLi Method
<code>
<?php
// mysqli
$mysqli = new mysqli("mydomain", "user", "password", "database");
$result = $mysqli->query("SELECT 'message' AS theMessage FROM 'messages'");
$row = $result->fetch_assoc();
echo $row['theMessage'];
</code>
Without getting into detail, right now at least, in the context of these examples, let’s at least break them down and see what you are doing and why.
In this first MySQLi example, there should be some standout items that are recognizable on first read.
<code>
$mysqli = new mysqli("db.mysite.com", "user", "password", "database");
</code>
You have $mysqli, which is a PHP variable being set to new mysqli with some parameters. You can gather from this that mysqli is a class and $mysqli will become an object once created. Let’s see if you can determine what the parameters of the class constructor are without Googling for an answer. The first parameter is “db.mysite.com”. The db in the subdomain stands for database so a logical guess would be that this first parameter is the database server. The next ones are straightforward: “user” is the username and “password” is the password for the user you are connecting with through PHP. The final parameter of “database” is, well... the name of the database. These four parameters are needed in order to create a valid MySQLi connection. They can be directly input, as in this example, or you can use variables such as $dbServer, $dbUser, $dbPass, and $dbName and store them in a separate file for your own organization. This will often be the case in PHP applications.
The next line is
$result = $mysqli->query("SELECT 'message' AS theMessage FROM 'messages'");
This sets $result equal to the result of the query method of the $mysqli object. You see this in the form of the syntax $mysqli->query. The properties that are passed make up the actual query you would like to send to MySQL. More on these queries later.
The next line is
$row = $result->fetch_assoc();
This sets the variable $row to the value of the object $result after the fetch_assoc() method is run. After the query, you can receive your results all at once or row by row from the server. To save resources on your server, you want all of the data at one time. This will allow you to use PHP to consume and parse the data however you please without bothering the database server more than you need to. The method fetch_assoc() is in a group of available methods for MySQL. These methods are
mysqli_fetch_assoc() fetches a result row as an associative array.
mysqli_fetch_array() fetches a result row as an associative array, a numeric array, or both.
mysqli_fetch_row() gets a result row as an enumerated array.
mysqli_fetch_object() returns the current row of a result set as an object.
In your example, $row is an array with associative values or key values for the array. This is different from a traditional array with numbered keys:
$row['firstname'] Vs $row[0]
firstname is the associative key value used to associate with the database column firstname. Now you have the variable $row set to the row or rows containing the data that you queried from the database.
The next line is
echo $row['_message'];
Here you use echo to show the results of the PHP function htmlentities on the variable $row, specifically the data in the ['_message'] id of the array. This is the specific data you are querying for.
PHP Communication with the PDO Method
Let’s see how the PDO version of this differs.
<code>
<?php
// PDO
$pdo = new PDO('mysql:host=localhost;dbname=myDatabase, 'user', 'password');
$statement = $pdo->query("SELECT 'message' AS theMessage FROM 'messages'");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo $row['theMessage'];
</code>
The first line is
$pdo = new PDO('mysql:host=localhost;dbname=myDatabase, 'user', 'password');
Here you create a new object named $pdo from the class PDO with a similar structure for passing the database host, database name, username, and password to the constructor.
The next line is
$statement = $pdo->query("SELECT 'message' AS theMessage FROM 'messages'");
Here you set the $statement in a similar way to the $pdo object method query.
The next line is
$row = $statement->fetch(PDO::FETCH_ASSOC);
In a similar fashion to MySQLi, the data is fetched to an associative array.
The last line is
echo $row['_message'];
It simply outputs the resulting data from the database.
So now that you know how to connect to MySQL TWO different ways, which one do you use?
There really is not that much of a difference in performance between these two ways. PHP.net’s documentation says “The impact is as low as 0.1%.” Here are some key advantages between MySQLi and PDO.
MySQLi Advantages
Asynchronous queries
Ability to get more info on affected rows
Proper database closing method
Multiple queries at once
Automatic cleanup with persistent connections
PDO Advantages
Useful fetch modes
Allowed to pass variables and values directly in to execute
Ability to auto-detect variable types
Option for automatically buffered results with prepared statements
Named parameters
The real difference comes when using a database system outside of MySQL or mariaDB. PDO supports 12 database types and MySQLi only deals in MySQL-specific functionality. Since you are using MySQL 8.0 and only want to use those functions, you are using MySQLi.
PHP Connection to a Database
Let’s go ahead and create a connection and test your database. First, let’s go to http://localhost/chapter4/seedDB.php.
Do you see the following?
Warning
mysqli::__construct(): (HY000/2002): No such file or directory in /var/www/chapter4/seedDB.php on line 4
Fatal error: Uncaught Error: mysqli object is already closed in /var/www/chapter4/seedDB.php:6 Stack trace: #0 /var/www/chapter4/seedDB.php(6): mysqli->query('Select * from u...') #1 {main} thrown in /var/www/chapter4/seedDB.php on line 6
Hmm, something must not be configured correctly. This is saying that there is an issue on line 4 of seedDB.php. Let’s go take a look.
<code>
<?php
require_once('db.php');
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_DATABASE);
</code>
Line 4 is the $mysqli = new mysqli line. This looks correct to me, so there must be an issue with the variables used in the mysqliconstructor. As you see on line 3, you are reading these variables from db.php. Let’s open that file.
<code>
<?php
$DB_HOST = '';
$DB_USER = 'root';
$DB_PASS = 'pass';
$DB_DATABASE = 'beginningPHP';
</code>
Ahh! Look at that! In line 2, $DB_HOST is set to ‘‘and not an actual host. If you remember, your host is set to db. Let’s go ahead and replace the empty space with db.
<code>
<?php
$DB_HOST = 'db';
$DB_USER = 'root';
$DB_PASS = 'pass';
$DB_DATABASE = 'beginningPHP';
</code>
Ok. If you save this and reload http://localhost/chapter4/seedDB.php, you should see some better results.
<code>
Creating table "USERS"Seeing Users into table..1..2..3
Users added
1 - tom - hanks - 2021-06-25 17:58:42
2 - billy - mitchell - 2021-06-25 17:58:42
3 - mega - man - 2021-06-25 17:58:42
</code>
When you are developing applications, it is good to have some dummy data on hand in order to test your code appropriately. The act of taking data (dummy or actual from production) and populating a database with it is called seeding. Here you are seeding the database beginningPGP, specifically the table users, with three rows of user information. In this case, you are using a simple .sql file with the data. In larger frameworks like Laravel, this is done through migrations and a program called artisan. This allows for you not only to seed the database with data, but for your development team to stay on the same page with your data by allowing these migrations to be accessed like you would access your code in git (version control). Once you have run this page, press refresh. What happens? The code checks first to see if the table exists and does not seed it with user information if it already exists. Let’s write some code that will show the users from this table.
Open showUsers.php in the chapter4 folder.
<code>
<?php
require_once('db.php');
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_DATABASE);
echo "No Results. Have you run <a href='http://localhost/chapter4/seedDB.php'>SeedDB</a>?";
}
</code>
Let’s go through this line by line.
<code>
<?php
require_once('db.php');
</code>
This is the standard beginning of a PHP file. You first require the db.phpfile to be loaded. Remember, this sets the variables for your database host, user, password, and database name.
<code>
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_DATABASE);
$query = "SELECT * FROM users";
</code>
<code>
$result = $mysqli->query($query);
</code>
Here you use the object $mysqli and its method query to submit your query to the database. The result will be set as the variable $result.
echo "No Results. Have you run <a href='http://localhost/chapter4/seedDB.php'>SeedDB</a>?";
}
</code>
This code may look complex but you are doing some pretty basic (for humans) logic. In programming languages, this type of “obvious to humans” logic takes precise logic handling in order to make sure that you account for all situations and stay away from errors. The if ($result) is PHP checking if $result evaluates to any “truthy” value. This can be
Boolean TRUE
Non-empty value
Non-NULL value
Non-zero number
You are basically asking if any useful data was found and returned to you. You will handle the scenario of nothing being returned a few lines down. First, let’s deal with the data you do have.
Here you echo out a header for the page using the HTML <h1> tags. Then you begin a while loop, which in PHP loop from beginning to end until a specified condition is meet. You can think of this like “while the traffic light is green, keep driving” or “while the pasta is not cooked, keep cooking.” Once either of those two conditions change (the traffic light becomes red or the pasta is cooked), the loop will stop. In your code, you are saying “while $row is equal to data fetched from the database as an associative array, run the loop.” Your loop is simple and it echoes your results from the database one row at a time. Once $row does not equal data from the database or the database is finished returning data, this loop will stop.
<code>
} else {
echo "No Results. Have you run <a href='http://localhost/chapter4/seedDB.php'>SeedDB</a>?";
}
</code>
This else corresponds to the if ($results) from above. This is what happens if $result comes back empty. When this happens, you want to return some kind of useful error to the user and not just standard MySQL or PHP errors. These types of errors can be used against you by attackers. Here you echo to the user that perhaps the database is empty and they may need to run the seedDBfile you ran earlier in order to put data into the database.
Here you see the use of MySQLi (as opposed to PDO) to connect PHP to the database, resulting in a database object named $mysqli. You want to select everything from the users table in your database so you use the query SELECT * FROM users. SELECT tells MySQL that you are requesting data. The * means everything. FROM tells MySQL where you want to get this data from, which is expected to be given as the next term. Finally, users is the table you want to get the data from. This is one of the most general queries you can do in MySQL. Let’s modify this a bit. What if you want to retrieve the list of names in alphabetical order by last name? Modify the query and run this:
<code>
$query = "SELECT * FROM users ORDER by last_name ASC"
</code>
This code is also found in showUsers2.php.
This query looks very similar to the first one but with some modifiers. After users you add ORDER, which tells MySQL that you would like to have your data returned in an ordered fashion. At this point, you have not told MySQL anything else. You need two factors for MySQL to be able to order these results. First, you need to tell MySQL which column of data you would like to have ordered. Currently, you have id, first_name, last_name, and created. In your query, you have ORDERED by last_name, which satisfies this first requirement, but now you need to tell MySQL which order. There are two main options: ascending (ASC) or descending (DESC). Ascending, when dealing with strings like last names, is A-Z because the numeric value for a is smaller than z so this is considered to be ascending. The reverse is DESC, which would be Z-A. If you run this code now, you should see this output:
<code>
Users in Database
Name: tom hanks = Created: 2021-06-28 14:17:45
Name: mega man = Created: 2021-06-28 14:17:45
Name: billy mitchell = Created: 2021-06-28 14:17:45
</code>
Another useful modifier for this query is LIMIT. Let’s say, for example, there are thousands of users in this database but you only want the top three ordered by score. This query would look like the following (also found in showUSers3.php):
<code>
$query = "SELECT * FROM users ORDER by score DESC LIMIT 3";
</code>
So far you have read from the database through the SELECTquery. The purpose of using a database in the backend of your website is for data to both read and store data. This is how photos show up on Instagram and how tweets enter the twitterverse. A user can take their tweet (data) and send it to the database where it gets stored in a table with specific values assigned to the associated columns. Let’s add another user to your database and you will see how this works. You will use PHP MySQL prepared statements. The advantage to using prepared statements is twofold:
1)
Over iterations of queries, there is reduced parsing time even though the query is run more than once, so the result is that the queries are executed with high efficiency.
2)
PHP MySQL prepared statements can be very useful against SQL injections.
Open up addUser.php and let’s break it down.
<code>
<?php
require_once('db.php');
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_DATABASE);
The first few lines should look familiar at this point. This is where you introduce the database variables stored in db.php and create an object named $mysqli from the mysqli class.
This line looks familiar but very different. This is your INSERT query, which you are creating for use as a prepared statement.
<code>
$query = $mysqli->prepare
</code>
Here you create a variable named $query, which is the result of the method prepare from the object $mysqli. prepare takes the query you want to run in MySQL but gives you the ability to bind parameters to minimize bandwidth on the server, as you only send the parameters each time and not the whole query. The query uses the verb INSERT, which has a structure of
You use the column structure of the userstable to insert values for first_name, last_name, age, and score. But where are the values? There are only question marks (?s). This is the binding element. MySQL looks at these question marks and sets that space aside for the specified amount of values to be assigned later; in your code, it is on the next line.
This code uses the $queryobject you created earlier and this time you use the bind_param method, which takes in two sets of parameters. The first (“ssii”) in your example is the list of the types of parameters that you are binding. You are using “ssii”, which stands for “string, string, integer, integer” or first_name, last_name, age, score. MySQL accepts four types:
i: integer (i.e., 1, 199, 4421)
d: double (1.0e6 to represent one million)
s: string (“pants”, “Bananas”)
b: BLOB (a binary large object is a varying-length binary string that can be up to 2,147,483,647 characters long)
Now that you have told MySQL what types of variables to expect, you list the variables you will be using.
<code>
$firstName = "Freddy";
$lastName = "Krueger";
$age = 40;
$score = 301;
</code>
Now you assign values to the variables you have already told MySQL you will be using for the query: two strings and two integers, just like you declared using “ssii”.
<code>
$query->execute();
$mysqli->close();
</code>
Lastly, you execute the query by calling the method execute from the $query object and then you close the connection to MySQL.
Go to http://localhost/chapter4/addUser.php and then back to http://localhost/chapter4/showUser.php to see the results. You should see one additional user in the table. If you refresh addUser multiple times, you will get multiple additions to the table. Now that you have some basic techniques for interacting with MySQL, in the next chapter you will take a deeper dive into more complex queries, data organization, and MySQL features.
Summary
In this chapter, you learned the basics of working with PHP and MySQL. You first learned how to connect to the DB using two methods such as MySQLi and PDO. You learned the advantages of using one method or the other. Finally, you explored the code needed to connect to the DB and show the users included in it.
In the next chapter, you will learn more about the data types you can utilize in the MySQL DB table, like CHAR and VARCHAR, and how to define multiple dependencies in queries.
