Skip to content
MPA CS

PHP - Connecting to MySQL

MySQL is the most common database to se in tandem with PHP. You can run it several ways, this guide will outline how to run a database server with MySQL on your host machine or with Docker.

Start MySQL

Section titled “Start MySQL”

MySQL by default runs on port 3306.

Option 1: Native

Section titled “Option 1: Native”

Follow the instructions at https://dev.mysql.com/doc/refman/8.0/en/installing.html. Note: you’ll need to install mysql (the client) and mysqld (the server daemon).

Example (dnf):

Terminal window
sudo dnf install mysql mysqld php-mysqld

Then, start the service.

Terminal window
sudo systemctl start mysql
# optional, enable the server to run on startup
sudo systemctl enable mysql

Connect to the MySQL server as root.

Terminal window
sudo mysql

Then, create a user with a database.

CREATE DATABASE mydb;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

You can test the connection with:

Terminal window
mysql -u myuser -p mydb --password=password

Option 2: Docker

Section titled “Option 2: Docker”

Install Docker if you haven’t already. Then, in the same directory as your project, create this Docker Compose file.

docker-compose.yml
services:
  db:
    image: mysql:8
    container_name: mysqldb
    environment:
      MYSQL_ROOT_PASSWORD: secure_password
      MYSQL_DATABASE: mydb
      MYSQL_USER: myuser
      MYSQL_PASSWORD: password
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql


volumes:
  mysql_data:

Run the above file with:

Terminal window
docker compose up -d

If you need to run the MySQL client in the container, run:

Terminal window
docker exec -it mysqldb mysql -u myuser -p mydb --password=password

SQL: CREATE TABLE, INSERT INTO, and SELECT

Section titled “SQL: CREATE TABLE, INSERT INTO, and SELECT”

For this demonstration, we will make an app that stores contacts.

Create contacts table

Section titled “Create contacts table”

https://www.w3schools.com/sql/sql_create_table.asp

CREATE TABLE contacts (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

Insert contact records

Section titled “Insert contact records”

Here’s an example of inserting a record.

INSERT INTO contacts (name, email) VALUES ('alice', 'alice@example.com');
INSERT INTO contacts (name, email) VALUES ('bob', 'bob@example.com');

Select contact record

Section titled “Select contact record”

You can retrieve the record(s) with the SELECT keyword.

-- select all columns
SELECT * FROM contacts;
-- or, just select some columns
SELECT name, email FROM contacts;
-- or, filter with the WHERE clause
SELECT name, email FROM contacts WHERE name='alice';

Connect to MySQL from PHP via PDO

Section titled “Connect to MySQL from PHP via PDO”

You will need to install the PDO MySQL extension.

Terminal window
sudo dnf install php-mysqlnd

Then, whenever you need to connect to the database, use the following. In a production-level system, you would put the database credentials in an environment, but for our learning purposes, we will just hard-code the values:

  <?php
    try {
      $dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4;port=3306';
      $pdo = new PDO($dsn, 'myuser', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      ]);
    } catch (PDOException $e) {
      die("Could not connect. " . $e->getMessage());
    }


    // TODO: Code goes here.


    // memory cleanup
    $pdo = null;
  ?>

Get and display contacts

Section titled “Get and display contacts”

Here’s a file that uses SELECT to retrieve records and then renders them in an HTML table.

contacts.php
<html>
  <head>
    <title>Contacts</title>
  </head>
  <body>


  <?php
    try {
      $dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4;port=3306';
      $pdo = new PDO($dsn, 'myuser', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      ]);
    } catch (PDOException $e) {
      die("Could not connect. " . $e->getMessage());
    }


    try {
      $sql = "SELECT name, email FROM contacts";
      // Execute the SQL query
      $result = $pdo->query($sql);
      // Process the result set
      if ($result->rowCount() > 0) {
        echo "<table><tr><th>Name</th><th>Email</th></tr>";
        // Output data of each row
        while($row = $result->fetch()) {
          echo "<tr>";
          echo "<td>" . $row['name'] . "</td>";
          echo "<td>" . $row['email'] . "</td>";
          echo "</tr>";
        }
        echo "</table>";
        unset($result);
      } else {
        echo "No records found.";
      }
    } catch(PDOException $e) {
      echo "Error: " . $e->getMessage();
    }


    $pdo = null;
  ?>


  </body>
</html>

Use a form to create records

Section titled “Use a form to create records”

Here are the two files from the Getting Started page.

index.php
<html>
<body>


<form action="welcome.php" method="post">
Name: <input type="text" name="name"><br>
E-mail: <input type="text" name="email"><br>
<input type="submit">
</form>


</body>
</html>
welcome.php
<html>
<body>


Welcome <?php echo $_POST["name"]; ?><br>
Your email address is: <?php echo $_POST["email"]; ?>


</body>
</html>

Let’s modify the welcome page to create the record if a POST request has occured, else display a message.

welcome.php
<?php
function h($s) { return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }


if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = isset($_POST['name']) ? trim($_POST['name']) : '';
    $email = isset($_POST['email']) ? trim($_POST['email']) : '';


    // Basic validation
    $errors = [];
    if ($name === '') { $errors[] = 'Name is required.'; }
    if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors[] = 'A valid email is required.';
    }


    if (empty($errors)) {
        try {
            $dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4;port=3306';
            $pdo = new PDO($dsn, 'myuser', 'password', [
               PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            ]);


            $stmt = $pdo->prepare('INSERT INTO contacts (name, email) VALUES (:name, :email)');
            $stmt->execute([':name' => $name, ':email' => $email]);


            $insertId = $pdo->lastInsertId();
            $message = "Record created (ID: {$insertId}). Welcome " . h($name) . "!";
        } catch (PDOException $e) {
            // In production, log $e->getMessage() and show a generic error.
            $errors[] = 'Database error: ' . h($e->getMessage());
        }
    }
}
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Welcome</title>
</head>
<body>
<?php if ($_SERVER['REQUEST_METHOD'] === 'POST'): ?>
    <?php if (!empty($errors)): ?>
        <h3>Errors</h3>
        <ul>
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    <?php else: ?>
        <p><?php echo h($message); ?></p>
        <p>Your email address is: <?php echo h($email); ?></p>
    <?php endif; ?>
<?php else: ?>
    <p>No form submitted. Please submit the form to create a record.</p>
<?php endif; ?>
</body>
</html>