How to Connect a MySQL Database to PHP (A Developer’s Guide)

PHP and SQL are two of the most commonly used tools in web development. PHP is a server-side scripting language that is used to create dynamic web pages, while SQL is a database management system that is used to store and retrieve data.

You don’t need to be a Linux expert to manage your servers anymore.

In this article, we will show you how to connect a MySQL database to PHP using the RunCloud dashboard.

We will also discuss two main ways to connect PHP to MariaDB: using mysql_connect and PDO.

Step 1: Create a Database

Before we can start using a database, we need to create a database on the server. We also need to create a user account with permissions to access this database. This can be done easily from the RunCloud dashboard itself.

Open the server on which you want to deploy your application, and click on the “Databases” button in the left menu.

On the next screen, click on “Database Users” and then select “Add New Database User” to create a new user account.

On the next screen, give a descriptive name to the user account and set a password. Be sure to note down these credentials as they will be needed later. Once you have provided the username and password, click “Save” to create the user.

Next, we need to create a database. Click on the “Databases” tab to view all the available databases. To create a fresh database, click on “Add New Database”.

When creating a database you will be asked to provide a name – this can be anything you like. For database collations, you can either leave it blank or refer to our documentation post: Understanding Database Collations.

Finally, from the drop-down menu, select the user account that we created earlier.

Once you have configured these settings, click on “Save”.

Step 2: Connecting to Web Applications

You must deploy a web application on the RunCloud server to run your PHP code. The process to create an application is very simple. In this tutorial we will be creating an empty web application, but the process is similar for any other type of application.

Firstly, go to your web application settings page in the RunCloud dashboard, and scroll down until you see the linked databases section.

Click on the drop-down button, select the database that we just created, and then save the changes.

Next, we will create a PHP file on your server that will host the code to connect to your database.

To do this, click on the File Manager tab in the left menu. Here you will see a list of all the files present in your application.

Click on New > File to create an empty file, and then give it the desired name.

Once you have created the file, click on it. This will open the text editor in a new window where you can edit the file.

Step 3: Coding Database Connection Module

The final step for using a database in your web application is to configure a pre-built library. This will allow you to perform database operations without needing to understand how the database handles these operations. There are two popular ways to do this:

Step 3.1: Use MySQLi

The mysqli connect is a PHP extension used to connect to MySQL databases. It is a simple and easy-to-use method that is ideal for small projects. It is also faster than PDO because it is a native PHP extension. However, it does not support all of the features of PDO.

To use this module, paste the following code snippet in the text editor. Make sure to replace the values of database, username, and password variables with the credentials that you noted down earlier.

<?php
$servername = "127.0.0.1";
$database = "my_app_dv";
$username = "my_db_user";
$password = ".GA8sn,tsP=?5Uy7Lqu#iBy:AzVs3KJE";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
mysqli_close($conn);
?>

Step 3.2: Use PDO

PDO is a PHP extension that provides a data-access abstraction layer. PDO also supports multiple database drivers, which makes it more flexible than mysqli connect. However, it is slower than mysqli connect because it is not a native PHP extension.

To use this method, copy the following code example and paste it into your text editor. Don’t forget to update the credentials in the first three lines with the values that you noted down earlier.

<?php
$database = "my_app_dv";
$username = "my_db_user";
$password = ".GA8sn,tsP=?5Uy7Lqu#iBy:AzVs3KJE";
$dsn = "mysql:host=127.0.0.1;dbname=$database;charset=utf8mb4";
$options = [
    PDO::ATTR_EMULATE_PREPARES => false, // Disable emulation mode for "real" prepared statements
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Disable errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Make the default fetch be an associative array
];
try {
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "Connected Successfully";
} catch (Exception $e) {
    error_log($e->getMessage());
    exit("Something bad happened");
}
?>

Step 4: Save and Test Connection

Once you have added the code to your server, click on “Save” to save the changes, and then visit the page in your browser. When you request this page, the server will execute the PHP code and establish a connection to the server.

The exact path of this page will vary depending on what you named the file. For instance, if the domain name of your website is https://www.example.com and you named your file sql.php then you can browse this file at https://www.example.com/sql.php.

If you see a “Connected Successfully” message then it means your code has successfully established a connection with your database, and you can now start using it in your code.

Wrapping Up

In this article, we have seen how to connect a MySQL database to PHP. We have outlined the steps needed for creating a database and granting permissions to user accounts before connecting to the database. We discussed two main ways to connect PHP to MariaDB: using mysqli connect and using PDO – both methods have their own benefits and drawbacks.

If you want to host your web application on the cloud, but aren’t sure where to start, then you should check out RunCloud.

RunCloud is a great platform for managing your servers, even if you are not a Linux expert.

With RunCloud, you can easily create web applications, manage databases, and connect PHP to MariaDB. So, what are you waiting for?

Sign up for RunCloud today and take your server management skills to the next level!