How to connect PHP with MySQL

Connecting PHP to MySQL is easy. It works on every hosting environment so it’s a perfect combination to get started with backend development. You can code it on every computer and don’t need big budgets, even if you need to manage a little more data already.

You can start with either topic. Let’s say first you start with PHP to get used to how programming works. With programming you can describe logic that is executed when someone visits your website. Using PHP you can dynamically access and modify your data.

The very next thing you should be familiar with, for this article, is obviously MySQL. Using MySQL you can describe how to store and access data in a very safe and scalable way.

Finally, in this article, we will investigate how to combine those to topics to make a good basement for dynamic, scalable, cheap backend applications.

Integrating PHP with MySQL: Step-by-Step Guide #

PHP has multiple decades of history. There’s multiple ways to connect to a MySQL database. I will use the object oriented approach. To establish the connection, you basically need this information:

1. The name of the database server (in many cases localhost)
2. The username and password
3. The database name
4. The port (in many cases 3306)

Having this information, in PHP you easily can do this:

1
2
3
4
5
6
7

$db = new mysqli(
    'localhost',
    'user',
    'password',
    'db1',
    3306,
);

Now using the variable $db you can communicate with your database. If you, for example, want to list all existing tables, you can do this

1
2
3
4

var_dump(
    $db->query('SHOW TABLES;')
        ->fetch_all()
);

With the function query you can send any SQL to the database and with fetch_all you can read all results and retrieve them as an array.

This code is using the mysqli-package of PHP, which should be available in most environments.

Extract credentials from code #

I would recommend not to have credentials in your codebase, you always should have them in some form of configuration file. For this simple demo, I’ll still have a PHP file called mysql.php which just returns an array:

1
2
3
4
5
6
7
8

<?php
    return [
        'db_host'     => 'localhost',
        'db_port'     => 3306,
        'db_user'     => 'database user',
        'db_password' => 'database password',
        'db_name'     => 'database name',
    ];

Now, in my code, I can just load this array and access the information:

1
2
3
4
5
6
7
8
9

<?php
    $config = include 'mysql.php';
    $db     = new mysqli(
        $config['db_hostname'],
        $config['db_user'],
        $config['db_password'],
        $config['db_name'],
        $config['db_port'],
    );

Example: PHP daily visit counter #

This little knowledge is already enough. Let’s do a simple visit-counter which is counting all visits per day. To do so, let’s go with this simple table:

1
2
3
4
5

CREATE TABLE visits
(
    visit_date  DATE PRIMARY KEY DEFAULT CURRENT_DATE,
    visit_count INT NOT NULL
);

Insert or update today’s counter #

Using this table, now on every visit, we either create the current day, or increase it’s counter by 1. Since we made visit_date the PRIMARY KEY the same date cannot exist twice, so we can always try to INSERT and just react to DUPLICATE KEY issues with an UPDATE as a fallback. In SQL it looks like this:

1
2
3
4

INSERT INTO visits (visit_date, visit_count)
    VALUE ("2025-06-04", 1)
ON DUPLICATE KEY
    UPDATE visit_count = visit_count + 1

This query will either create a new day 2025-06-04 with 1 as visit_count or increase the current visit_count by 1. In PHP this all looks like this:

1
2

    $today = date('Y-m-d');
    $db->query('INSERT INTO visits (visit_date, visit_count) VALUE ("'.$today.'", 1) ON DUPLICATE KEY UPDATE visit_count = visit_count + 1');

Show visits of last 10 days #

Now there’s just one last step, to load the last visits and show them. As shown in the intro already, you can use query and fetch_all to get the data:

1
2

    $dailyVisits = $db->query('SELECT visit_date, visit_count FROM visits ORDER BY visit_date DESC LIMIT 10;')
                      ->fetch_all(MYSQLI_ASSOC);

Wrap-up: PHP daily visit counter #

If you now put all together, you should end up with a code like this. As you can see I extracted out the credentials as a standalone config file and I clearly seperated HTML from my PHP code.

<?php
    $config = include 'mysql.php';
    $db     = new mysqli(
        $config['db_hostname'],
        $config['db_user'],
        $config['db_password'],
        $config['db_name'],
        $config['db_port'],
    );

    $today = date('Y-m-d');
    $db->query(
        'INSERT INTO visits (visit_date, visit_count) VALUE ("' .
        $today .
        '", 1) ON DUPLICATE KEY UPDATE visit_count = visit_count + 1'
    );

    $dailyVisits = $db->query('SELECT visit_date, visit_count FROM visits ORDER BY visit_date DESC LIMIT 10;')
                      ->fetch_all(MYSQLI_ASSOC);
?>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Simple PHP MySQL day based visit counter</title>
</head>
<body>
<table>
    <tr>
        <th>Date</th>
        <th>Visits</th>
    </tr>
    
    <?php foreach ($dailyVisits as $visitData): ?>
        <tr>
            <td><?php echo $visitData['visit_date']; ?></td>
            <td><?php echo $visitData['visit_count']; ?></td>
        </tr>
    <?php endforeach; ?>
</table>
</body>
</html>

Checkout the online-demo of a php mysql daily visit counter.