How to connect PHP to MySQL database

Connecting your PHP code to MySQL databases is something you will encounter very often if you are creating online forms. When users enter any data into the fields of your forms, you need to collect that information and add it to the database.

To do that, you want to know how to connect PHP with MySQL. In this article, we are covering two most popular ways to establish this connection.

Connecting PHP to MySQL

In order to connect a MySQL database to PHP, you require MySQL on your computer, a tool to create and manage databases, and PHP installed. The most popular ways to connect a PHP script to MySQL are MySQli and PDO.

First, let’s look at the beginner-friendly MySQLi tool.

Let’s start with creating a MySQL database that we will be connecting to using PHP. We’ve employed TablePlus to do the job. This app is a handy database manager that can help you handle databases for various purposes in a single interface. And you can create a database and add information to it in just a few clicks through the app’s user-friendly interface.

Just open the app, click the database icon in the app’s window menu, and then click New… Type in the name of your new database and click OK.

Create a MySQL connection

Now, let’s connect to the database with PHP using mysqli_connect to do that. You will also need the password to your MySQL database, so make sure you have that. We store our credentials in Secrets, so they are easy to access, we don’t lose them, while at the same time they are safe from unwanted access:

Now, we are all set and ready to connect our MySQL database to PHP.

Open your favorite tool to work with PHP and create an index.php file in your desired destination. We use CodeRunner to write and edit our code in various languages, so we will connect PHP to MySQL with the help of this app.

Here’s the code we used to connect through MySQLi extension:

<?php
    $conn = mysqli_connect(
        <location-of-your-database>, 
        <your-MySQL-database-username>, 
        '<your-MySQL-database-password>',
        'Connect');
    if(!$conn){
        echo 'Connection error: ' . mysqli_connect_error();
    }
?>

Click Run in the top menu panel of CodeRunner to run the code and view your results. If there’s no error, your PHP script worked for establishing MySQL database connection, congrats!

Before you run this code, make sure you have PHP installed on your system. If not, just type in “brew install php” in Terminal.

With the connection established, we can perform actions with the database.

To query our database, we just need to connect to the database as before and request the information we need:

<?php
    $conn = mysqli_connect(
        <location-of-your-database>, 
        <your-MySQL-database-username>, 
        '<your-MySQL-database-password>, 
        'Connect');
    if(!$conn){
        echo 'Connection error: ' . mysqli_connect_error();
    }
    $sql = 'SELECT id FROM connect_table';
    $result = mysqli_query($conn, $sql);
    $connect = mysqli_fetch_all($result, MYSQLI_ASSOC);
    print_r($connect);
?>

Notice that we’ve used SELECT syntax to find the exact column we need to view data from our table.

How to insert records

Now, let’s move to PHP to MySQL connect example that inserts information into our database.

Use INSERT INTO … VALUES syntax for that:

Here’s the code snippet:

<?php
    $conn = mysqli_connect(
        <location-of-your-database>, 
        <your-MySQL-database-username>, 
        '<your-MySQL-database-password>, 
        'Connect');
    if(!$conn){
        echo 'Connection error: ' . mysqli_connect_error();
    }
    $sql = 'INSERT INTO connect_table VALUES (5)';
        if ($conn->query($sql) === TRUE) {
        echo "Record added!";
    } else {
        echo "Error: " . $sql . "
" . $conn->error;
    }
    $conn->close();
?>

Add your own values and run the code in your PHP file.

You can save the above snippet for later to avoid digging it up again. We save our code bits and snippets in SnippetsLab app. It helps organize code bits and never lose any code samples we might need later.

How to update records in a database from PHP script

In order to update your records in a MySQL database using a mysqli connection to PHP, you need to use UPDATE … SET … WHERE syntax.

Specify which column and line you want to update and with what value, and run your code:

Here’s the code we used for this action:

<?php
    $conn = mysqli_connect(
        <location-of-your-database>, 
        <your-MySQL-database-username>, 
        '<your-MySQL-database-password>, 
        'Connect');
    if(!$conn){
        echo 'Connection error: ' . mysqli_connect_error();
    }
    $sql = 'UPDATE connect_table SET id = 66';
        if ($conn->query($sql) === TRUE) {
        echo "Record updated!";
    } else {
        echo "Error: " . $sql . "
" . $conn->error;
    }
    $conn->close();
?>

How to delete queries from the PHP script

Next, let’s see how we can quickly remove any unwanted entries in our database with our established PHP database connectivity.

The syntax for deletion in MySQLi is DELETE FROM … WHERE …, so let’s try it out in our code.

For example, if we want to delete value 54 from our connect_table in the Connect MySQL database, we can use the following code:

We get "Value deleted!" printed back, meaning the action was successful, and we can recheck it in our TablePlus database view:

As you can see, value 54 is now removed from the id column.

Connect using PDO

Another popular option when it comes to connecting from your PHP projects to MySQL is PDO (short for PHP Data Objects). This method is more universal as it works with multiple SQL databases, and not just MySQL, unlike MySQLi.

PDO MySQL connection can be established with the following code:

Here’s the code we used to perform the connection:

<?php
$servername = "localhost";
$username = "<your-database-username>";
$password = "<your-database-password>";
try{
    $conn = new PDO("mysql:host=$servername;dbname=<the-name-of-your-database",$username,$password);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    echo "Yay, you are connected to server!";
    }catch(PDOException $e){
        echo $e.getMessage();
    }
?>

Now that you are connected to the database, you can add PDO actions to your code, e.g. insert, delete, select, or update.

Test this out by creating a simple PHP form and submit your values through it.

Conclusion

Thank you for following our tutorial, now you know two of the most popular ways to connect PHP and MySQL — MySQLi and PDO connection to a SQL database.

PHP — MySQL connection is a versatile tool that can help you retrieve data from a database, update a database, and collect data from your users and add that to your database.

If you are just getting started with connecting PHP to MySQL, we recommend trying MySQLi. Once you get more comfortable with the process, you can add in PDO as it can be used with other databases, and not just MySQL.

As you are working on your code, you can use code editor CodeRunner to write and execute your code, SnippetsLab — to save any of your code bits for later usage, and TablePlus — to manage your databases. As for the login credentials to your databases, they can be safely stored in Secrets, an app for storing passwords, credit card and bank account information.

One more tool you can try to help you work with PHP is Whisk, an app with live preview of your pages — it lets you create and make adjustments in real time. So if you need to make a PHP form for your project, you can use this app for the task.

All of these apps are available with a Setapp subscription. Setapp is a service of productivity tools for Mac and iOS that strives to clear routine tasks off your schedule and free up the space for new and exciting endeavors. You can try it for free with the 7-day trial and explore these and more tools for daily tasks.