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

Connecting a MySQL database with a PHP website can be challenging, especially for beginners. Developers must ensure they have the correct credentials and that the connection is properly configured.

After the connection is set up, developers may encounter errors when retrieving or manipulating data from the database. Debugging these errors can be time-consuming. Security is also a major concern to prevent unauthorized access to sensitive data.

This blog post will discuss how to connect a MySQL database on different servers, addressing different methods and providing step-by-step instructions for localhost, Cloudways server, PDO, and Remote MySQL.

Try Cloudways’ Google Compute Engine Servers!

Supercharge your website with lightning-fast performance and rock-solid reliability!

Start Today

Brief Overview of MySQL

MySQL is a widely popular open-source relational database management system (RDBMS) that excels in storing, managing, and retrieving data. It is renowned for its efficiency, versatility, and widespread adoption.

MySQL, when combined with PHP cloud, employs a structured approach to data organization. It arranges data into tables consisting of rows and columns with defined data types. Relationships between tables are established through primary and foreign keys, ensuring data integrity and enabling complex data querying.

MySQL’s adaptability is demonstrated by its cross-platform compatibility, operating seamlessly across various operating systems. Its extensive community and ecosystem contribute to its ongoing refinement and integration with other tools and technologies.

Why Should You Use MySQL?

MySQL is a popular choice for data management in various applications, and there are several compelling reasons for this. It is known for its exceptional performance and scalability, efficiently handling high-volume transactions and read-heavy workloads.

Another reason to use MySQL is its widespread adoption and extensive ecosystem. As one of the most popular relational database management systems globally, MySQL has a vast community of developers, translating to a wealth of available resources, tutorials, and plugins to integrate it with various programming languages, frameworks, and tools.

MySQL also provides advanced security features to safeguard sensitive data. It supports encryption, user authentication, and role-based access control, ensuring that only authorized users can access the database and its information.

It also prioritizes database observability alongside its advanced security features. This includes encryption, user authentication, and role-based access control, ensuring that only authorized users can access and monitor the database’s information effectively.

There are 3 types of methods in PHP to connect MySQL database through the backend:

1. MySQL
2. MySQLi
3. PDO

mysql() is now obsolete because of security issues like SQL injection etc, but the other two are being actively used.

1. MySQLi

MySQLi is an API that serves as a connector function, linking the backend of PHP applications to MySQL databases. It is an improvement over its predecessor, offering enhanced safety, speed, and a more extensive set of functions and extensions.MySQLi was introduced with PHP 5.0.0, and its drivers were installed in version 5.3.0. The API was designed to support MySQL versions 4.1.13 and newer.

2. PDO

The PHP Data Objects (PDO) extension is a Database Abstraction Layer that serves as an interface for the backend to interact with MySQL databases. It allows for changes to be made to the database without altering the PHP code and provides the flexibility to work with multiple databases. One of the significant advantages of using PDO is that it keeps your code simple and portable.

Should I Use MySQLi or PDO?

Both MySQLi and PDO are popular choices for interacting with databases in PHP. The decision between the two often comes down to personal preference and project requirements.

MySQLi is a PHP extension specifically designed to work with MySQL databases. It offers both procedural and object-oriented interfaces, making it easy to transition from the older MySQL extension. MySQLi also provides support for prepared statements and transactions, which can help improve security and performance.
Here’s a simple example how to use MySQLi:

<?php
// MySQLi connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// SQL to create table
$sql = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

// Execute query
if ($mysqli->query($sql) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

PDO, on the other hand, is a database abstraction layer that supports multiple database systems, including MySQL, PostgreSQL, SQLite, and others. It provides a consistent API for working with different databases, making it a good choice if you need to switch between database systems easily. PDO also supports prepared statements and offers features like error handling and fetching data in different formats.

here’s how you could do it using PDO:

<?php
// PDO connection
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// SQL to create table
$sql = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

// Execute query
try {
    $pdo->exec($sql);
    echo "Table 'users' created successfully";
} catch(PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
}

// Close connection
$pdo = null;
?>

System Requirements of MySQL

The Enterprise Service Manager has certain system requirements that are recommended for optimal performance. These requirements are outlined below.

Linux x86 64-bit	1.3 GB	800 MB
macOS	1.2 GB	700 MB
Windows x86 64-bit	800 MB	500 MB

 

Optimize Your Database Performance With Cloudways

Sign up now and master the art of connecting MySQL databases with PHP hosting today!

TRY NOW

Create MySQL Database at Localhost

Before you start building a PHP connection to a MySQL database, you need to know what PHPMyAdmin is. It’s a control panel from which you can manage the database you’ve created. Open your browser, go to localhost/PHPMyAdmin, or click Admin in XAMPP UI.

After installing XAMPP, you must add a password to your account for added security. To do this, navigate to the User Account section and locate the username shown in the image below. From there, you can set a password for your account.

To set a password for your XAMPP account, click on the Edit Privileges button and navigate to the Change Admin Password section. Enter your password and click Save to update your account. Be sure to remember this password, as you’ll need it to connect to your database.

💡 Note: While it is not strictly necessary to change the password for accessing databases on your localhost, it is considered good practice to do so. For this reason, we have chosen to use a password for added security.

1. Create Database

Now return to the homepage of PHPMyAdmin. Click the New button to create a new database.

In the new window, enter a name for your database. For this tutorial, we’ll name it “practice”. Next, select utf8_general_ci as the collation, as this will handle all the queries and data covered in this tutorial series. Finally, click on Create to create your new database.

Currently, the newly created database is empty because it contains no tables. In the upcoming series, we’ll learn how to create tables and insert data into them. Let’s focus on connecting the database to localhost using PHP.

2. Create a Folder in htdocs

First, navigate to the XAMPP installation folder and open the htdocs subfolder (usually located at C:\xampp). Inside htdocs, create a new folder named “practice” where we’ll store our web files. We must create a folder in htdocs because XAMPP uses the folders within htdocs to execute and run PHP sites.

💡 Note: If you’re using WAMP instead of XAMPP, make sure to create the practice folder within the C:\wamp\www directory.

3. Create Database Connection File in PHP

Create a new file named db_connection.php and save it as a PHP file. We need a separate file for the database connection because it allows us to reuse the same connection code across multiple files. If several files need to interact with the database, you can simply include the db_connection.php file instead of writing the connection code multiple times.

<?php
function OpenCon()
{
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$db = "example";
$conn = new mysqli($dbhost, $dbuser, $dbpass,$dbname) or die("Connect failed: %s\n". $conn -> error);
return $conn;
}
function CloseCon($conn)
{
$conn -> close();
}
?>

Let’s take a closer look at the variables used in our db_connection.php file and their purpose:

1. $dbhost: This variable specifies the host where your RAID server is running. It’s usually set to localhost.
2. $dbuser: This variable specifies the username for accessing the database. For example, it could be set to root.
3. $dbpass: This variable specifies the database password. It should be the same password you use to access PHPMyAdmin.
4. $dbname: This variable specifies the database name you want to connect to. In this tutorial, we created a database with a specific name, so you should use that name here.

To use the database connection in your code, simply include the connection.php file at the top of your script using PHP’s include function (e.g., include ‘connection.php’). This allows you to call and use the connection functions throughout your code.

If you need to change the connection details, you only have to update them in one place, and the changes will automatically apply to all other files that include the connection.php file.

4. Check Database Connection

To connect to your database, create a new PHP file named index.php and add the following code to it:

<?php
include 'db_connection.php';
$conn = OpenCon();
echo "Connected Successfully";
CloseCon($conn);
?>

To view the index page, open your web browser and navigate to localhost/practice/index.php. You should see the following screen:

5. See Confirmation Message

Great job! You’ve successfully connected your database to your localhost. If you’re unable to see the expected screen, double-check the contents of your db_connection.php file to ensure everything is set up correctly.

How to Create MySQL Database on Cloudways

For this tutorial, it’s assumed that you have a PHP application installed on the Cloudways server. Here’s the setup that I’ll be using:

If you’re not a Cloudways user, try for free by signing up for an account and following the simple GIF to set up your server and PHP application. Cloudways offers different IaaS providers like AWS, Google Cloud, and DigitalOcean. Also, it supports a variety of applications including PHP, Laravel, Woocommerce, WordPress etc. so you can choose the one that best fits your needs.

After successfully launching your PHP application on Cloudways, navigate to the Application tab to view the details of your database. You can also click on the Launch Database Manager button to access the database manager.

Step 1. Connect to MySQL Database

• To connect to the database, use the mysql_connect function.
• This returns a pointer, or database handle, to the connection.
• You’ll use this handle later in your code. Don’t forget to add your database credentials once you have the handle.
• Create a new PHP file named db_connection.php. This file will contain the code for connecting to the database. Creating a separate file for the database connection allows you to reuse the same connection code across multiple files.
• To use the connection in your code, simply include the db_connection.php file at the top of your script using PHP’s include function (e.g., include ‘db_connection.php’).
• After that, you have two options for connecting to the database: PHP MySQLi Procedural or PHP PDO-Based Database.

1.1 For PHP MySQLi Procedural

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$db = "dbname";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$db);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

1.2. For PHP PDO-Based Database

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$db = "dbname";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password, $db);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>

Step 2. Check Database Connection

<?php
include 'db_connection.php';
echo "Connected Successfully";
mysqli_close($conn);
?>

For PDO, close the connection like this:

$conn = null;

Step 3. How to Establish Remote MySQL Connection

Follow these simple steps to establish a secure remote PHP MySQL connection using the Cloudways Platform. This will allow you to access and manage your databases from anywhere without compromising security.

1. Log into the Cloudways Platform using your credentials.
2. Click on the “Servers” tab in the top menu bar and select your target server from the list.
3. In the left menu, navigate to the “Security” option and click on the “MySQL” tab.
4. In the “Add IP to Whitelist” text area, enter the IP address you want to add and click on the “Add” button. If you have multiple IP addresses to add, repeat this step for each one.
5. Once you have added all the necessary IP addresses, click the “Save Changes” button to finalize your changes.

After successfully setting the IP Whitelisting, set the IP address in connection and run the query.

Experience Managed PHP Hosting With Ease!

Supercharge your website with lightning-fast performance and rock-solid reliability!

TRY NOW

3 Best MySQL Management Tools in 2024

Managing MySQL databases can be complex but can be made much simpler with the right tools. Many tools are available that provide a graphical user interface (GUI) to make working with MySQL databases more accessible for developers. Here are a few of them!

1. MySQL Workbench

MySQL Workbench is a powerful visual tool designed for database architects, developers, and administrators. It provides many features, including data modeling, advanced SQL editing, and administration tools for server configuration, user management, backup, and more.

It is available for Windows, Linux, and macOS X operating systems. Its user-friendly interface and robust functionality have become a popular choice among developers for managing their databases.

Pros

• Saves SQL statements for easy access and reuse.
• offline access to remote databases.
• store multiple connections in one convenient location.
• visual schema and query builder for easy database design and management.

Cons

• more complex to use than other database management tools, such as PHPMyAdmin.
• software crashes while using MySQL Workbench.

2. Navicat for MySQL

Navicat for MySQL is a graphical tool for managing databases. It connects to multiple databases, including PHP, MySQL, and MariaDB. It also works with cloud databases like Amazon RDS and Google Cloud.

Navicat for MySQL has advanced features and a user-friendly interface. It is ideal for managing cloud-based applications. Key features include data modeling, SQL editing, and server configuration.

Pros

• Intuitive and easy-to-use user interface.
• easy connection to MySQL databases via SSH terminal.
• schedule database jobs, including backup, restore, and report generation.
• data import and export from various formats, including ODBC, Excel, Access, DBF, TXT, CSV, XML, and JSON.
• visual schema and query builders for easy database design and management.
• Compatible with Windows, Linux, and Mac OS operating systems.
• team collaboration feature for working with others.

Cons

• expensive.
• slow down the IDE.
• time-consuming.

3. SQLyog

SQLyog is designed to make it easy for MySQL administrators to efficiently manage their database tasks. It is available exclusively for the Windows operating system. SQLyog is a reliable database management tool with a user-friendly interface and robust functionality.

Along with handy synchronization and backup options, SQLyog’s latest update comes with an SQL editor with auto-completion, error checking, and syntax highlighting, which makes it the best choice for trouble-free query processing.

Pros

• Smooth query designer tool available
• advanced features that help in learning database administration.
• Full-featured SQL editor with built-in query optimizer.
• Synchronization and backup options.

Cons

• Doesn’t support databases except for MySQL.
• No native support for Linux and MAC operating systems.
• No drag-and-drop support between multiple tabs.

Summary

A well-designed database is important for any application. A MySQL database is needed to store and manage information for things like content management systems or online task-tracking platforms.

When it comes to choosing between MySQLi and PDO, both have their advantages. However, it’s important to remember that MySQLi is specifically designed for connecting MySQL with PHP. You must modify your entire codebase if you need to migrate to another database.

On the other hand, PDO is compatible with 12 different databases, making migration much easier. Ultimately, the choice between MySQLi and PDO will depend on your specific needs and preferences.