MySQL Database

A dynamic and complex website is usually driven by a database of some sort. In this lesson, we'll learn how to work with a database of our own, using one of the most popular database management systems around, MySQL.

Overview of MySQL

MySQL is used so widely with PHP because it is easy-to-use, fast, secure, scalable and extremely powerful. In addition, it runs on a wide range of operating systems and therefore is the ideal database management system to use for applications and websites of all sizes.

The logo of MySQL.

MySQL databases store their data into individual tables, each divided up into rows and columns. Columns define what kind of data the rows hold, and each row represents an entry into that table.

Here's an example of how a table is structured in MySQL:

HTML
+----+------------+----------------------------------+
| id | username   | email                            |
+----+------------+----------------------------------+
|  1 | AceHood    | [email protected]                 |
|  2 | JohnCena   | [email protected]                |
|  3 | Drake      | [email protected]                   |
|  4 | Beyonce    | [email protected]                 |
|  5 | Nas        | [email protected]                     |
+----+------------+----------------------------------+

In this example table, we have three columns:

1. id: The user's identification number.
2. username: The username of the account.
3. email: The user's email address.

Then we have five rows, each row being a completely separate user.

Connecting to a MySQL Server

To work with a MySQL database, we first must be able to connect to a MySQL server. Thankfully, this is a relatively simple thing to do. The basic syntax for it looks like this:

PHP
new PDO('mysql:host=localhost;', $username, $password);

You simply pass in a string containing the MySQL server name and then the username and password of the account you're trying to access that server with.

Create a separate file called database.php to hold our database details, and put this in it:

PHP
<?php
    $host = 'localhost';
    $username = 'username';
    $password = 'password';

    $pdo = new PDO('mysql:host=$host;', $username, $password);
?>

Fill in the variables with your own details and you should be good to go and ready to work with your MySQL database by simply importing this file whenever you want to use the newly created $pdo variable:

PHP
<?php
    include 'database.php';

    // you can now access $pdo
?>

Creating a MySQL Database and Table

Unless you already did so, you'll need to create a database on the database server. This is because a single server can host many different databases. Let's create a database using PHP.

PHP
<?php
    include 'database.php';

    $database_name = 'app';
    $query = "CREATE DATABASE IF NOT EXISTS " . $database_name;
    $pdo->exec($query);

    echo('Database created successfully.');
?>

The text you see here, CREATE DATABASE IF NOT EXISTS is something called SQL, which stands for Structured Query Language. Simply put, it is how we can describe commands for MySQL to follow and do the stuff we want it to do.

In this case, we are telling MySQL to create a new database if one with that same name does not already exist, and then telling it the name of the database to create, app.

Now app is an empty database on our database server. We need to define a table for our users on it. Here's how to create a users table with our three columns on our app database:

PHP
<?php
    include 'database.php';

    $table_name = 'users';
    $query = "CREATE TABLE `" . $table_name . "` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(45) NULL,
        `email` VARCHAR(45) NULL,
        PRIMARY KEY (`id`));";
    $pdo->exec($query);

    echo('Table created successfully.');
?>

Without getting too complicated, that command used the CREATE TABLE to create a table with our desired name, users. Then it added a column named id as an integer that auto-increments (each new entry gets a unique number that is just the increment of the row's id), followed by some text for the username and some more text for the email. Finally, the query was executed and our table was created successfully!

Deleting a MySQL Database and Table

Before moving on to the fun stuff, it is worth learning how to undo what we just did. In other words, we should learn how to delete a MySQL database and table.

Here's how to delete, or drop, the MySQL table we just created:

PHP
<?php
    include 'database.php';

    $table_name = 'users';
    $query = "DROP TABLE IF EXISTS " . $table_name;
    $pdo->exec($query);

    echo('Table dropped successfully.');
?>

Running that script will drop the database with the name you provided. Now here's how to delete the entire database, along with every table that might exist on it:

PHP
<?php
    include 'database.php';

    $database_name = 'app';
    $query = "DROP DATABASE IF EXISTS " . $database_name;
    $pdo->exec($query);

    echo('Database dropped successfully.');
?>

As you can tell, the commands to drop a table and database are basically identical, and therefore you should be very careful when using either command!

Inserting Data into a MySQL Database Table

Assuming we still have both our MySQL database and table, let's learn how to insert data into them. Inserting data involves specifying the columns you want to insert data into, and then providing the values for each column.

Let's add a new user to our app's database:

PHP
<?php
    include 'database.php';

    $username = 'Biggie';
    $email = '[email protected]';

    $query = 'INSERT INTO
        users (username, email)
        VALUES (:username, :email)';

    $params = [
        'username' => $username,
        'email' => $email
    ];

    $statement = $pdo->prepare($query);
    $statement->execute($params);

    echo('User added successfully.');
?>

First we manually defined the values that we wanted to insert. In a real application, this might come from user input. After that, we define the query to run. Since we are inserting data, we use the INSERT INTO SQL statement and pass in the table we want to use, users. Then we define the two columns we want to insert data into, the username and email columns, along with two placeholder variables, :username and :email. The value of these variables are then provided by the following $params array.

Finally we can prepare the statement and then execute it using the desired parameters.

If all went well, your database should now have a new entry at the bottom and look like this:

HTML
+----+------------+----------------------------------+
| id | username   | email                            |
+----+------------+----------------------------------+
|  1 | AceHood    | [email protected]                 |
|  2 | JohnCena   | [email protected]                |
|  3 | Drake      | [email protected]                   |
|  4 | Beyonce    | [email protected]                 |
|  5 | Nas        | [email protected]                     |
|  6 | Biggie     | [email protected]                  |
+----+------------+----------------------------------+

Getting Data from a MySQL Database Table

Great, now let's learn how to get data from a MySQL table now that we've inserted some. To get some data back, we use the SELECT SQL statement and define the condition for it. Let's say we want the first user in our table:

PHP
<?php
    include 'database.php';

    $id = 1;

    $query = 'SELECT
        id,
        username,
        email
        FROM users
        WHERE id = :id';

    $statement = $pdo->prepare($query);
    $statement->bindParam(':id', $id);

    $statement->execute();
    $data = $statement->fetch(PDO::FETCH_ASSOC);

    print_r($data);
?>

HTML
Array (
    [id] => 1
    [username] => AceHood
    [email] => [email protected]
)

We're using the SELECT SQL statement, passing along the exact columns we want from the row, specify the table as FROM users then use a WHERE SQL statement to define that we only want the row where the id matches the value we defined, 1.

Our code prepares the statement, the binds the parameter, executes it, then returns the data in an array that we put in the $data variable. Finally, we print out that variable to see that contents are exactly what we wanted.

Updating Data on a MySQL Database Table

You can update data on a MySQL database table by using the UPDATE SQL statement.

PHP
<?php
    include 'database.php';

    $id = 1;
    $username = 'Common';
    $email = '[email protected]';

    $query = 'UPDATE users SET
        username = :username,
        email = :email
        WHERE id = :id';

    $params = [
        'id' => $id,
        'username' => $username,
        'email' => $email
    ];

    $statement = $pdo->prepare($query);
    $statement->execute($params);

    echo('User updated successfully.');
?>

As usual, the values are provided in a variable for demonstration purposes, but in a more developed application, this might come from user input. We are setting new data on the row whose id is equal to 1. By the end of this script, our database should look like this:

HTML
+----+------------+----------------------------------+
| id | username   | email                            |
+----+------------+----------------------------------+
|  1 | Common     | [email protected]                  |
|  2 | JohnCena   | [email protected]                |
|  3 | Drake      | [email protected]                   |
|  4 | Beyonce    | [email protected]                 |
|  5 | Nas        | [email protected]                     |
|  6 | Biggie     | [email protected]                  |
+----+------------+----------------------------------+

Deleting Data from MySQL Database Table

When you want to delete data, you're usually only trying to delete data within a table, not the entire table nor the entire database. For this case, we can use the DELETE FROM SQL statement.

Let's say we want to delete our last entry in our table, the 6th entry:

PHP
<?php
    include 'database.php';

    $id = 6;
    $query = 'DELETE FROM users WHERE id = :id';

    $params = [
        'id' => $id
    ];

    $statement = $pdo->prepare($query);
    $statement->execute($params);

    echo('User deleted successfully.');
?>

By now the syntax should be familiar. We are asking MySQL to delete data from the users table wherever the row's id column is equal to 6. That deletes the 6th entry in our table, leaving the table finally looking like this:

HTML
+----+------------+----------------------------------+
| id | username   | email                            |
+----+------------+----------------------------------+
|  1 | Common     | [email protected]                  |
|  2 | JohnCena   | [email protected]                |
|  3 | Drake      | [email protected]                   |
|  4 | Beyonce    | [email protected]                 |
|  5 | Nas        | [email protected]                     |
+----+------------+----------------------------------+

Conclusion

Database management systems are powerful and flexible tools that allow for dynamic and robust websites and applications. They manage and store our data, following the commands we give them for manipulating and serving that data to us. This was just an introduction to the world of databases, but hopefully this helped you get started with the basics of working with MySQL in PHP!

Resources

• PHP Data Objects on php.net
• PHP Data Objects interface on dev.mysql.com

• Support Us
• Discord
• Share
• Post
• Share