How to Connect MySQL Database to PHP Using MySQLi and PDO?

Connecting a MySQL database to your PHP website can be challenging, especially for beginners. Ensuring accurate login information and proper setup is crucial to avoid errors.

Even after establishing a PHP database connection, retrieving data or making changes can be problematic. Additionally, security concerns and protecting data from hackers are always present.

This post provides step-by-step instructions for two methods to connect a MySQL database on different servers, catering to both localhost and Cloudways server environments.

Why Should You Use MySQL?

MySQL is a popular choice for data management due to several key factors. Its exceptional performance and scalability ensure efficient handling of high-volume transactions and read-heavy workloads.

Furthermore, MySQL’s widespread adoption has resulted in a rich ecosystem. This translates to a wealth of resources, tutorials, and plugins that simplify integration with various programming languages, frameworks, and tools.

Security is another MySQL strength, with features like encryption, user authentication, and role-based access control ensuring data protection. Additionally, built-in tools for database observability allow developers to monitor and optimize performance effectively.

Seamless PHP & MySQL Hosting on Cloudways

Effortlessly connect your PHP applications to MySQL with our optimized hosting. Enjoy lightning-fast performance, built-in caching, and Cloudflare Enterprise for security.

Try Free

Connecting to MySQL Database in PHP

PHP offers three primary methods for MySQL database connection:

• MySQL (obsolete due to security vulnerabilities)
• MySQLi
• PDO

MySQLi and PDO are actively supported and widely used.

1. MySQLi

MySQLi, a PHP extension tailored for MySQL databases, was launched with PHP 5.0.0. Supporting MySQL versions 4.1.13 and later, it delivers enhanced features and functionality. MySQLi also provides advancements in security, speed, and flexibility.

It supports both procedural and object-oriented programming styles, easing the transition from the older mysql() extension.

MySQLi also includes advanced features like prepared statements and transactions for enhanced security and performance.

2. PDO (PHP Data Objects)

The PHP Data Objects (PDO) extension acts as a database abstraction layer, providing a consistent Application Programming Interface (API) for interacting with various database types, including MySQL, PostgreSQL, SQLite, and more.

This allows developers to switch between different databases without significant code changes.

PDO supports prepared statements, error handling, and fetching data in different formats. Its portability makes it a great choice for projects that may require future support for multiple database types.

Should I Use MySQLi or PDO?

Both MySQLi and PDO are excellent choices for PHP-MySQL interaction. The best choice depends on project requirements and coding style preferences.

• MySQLi is ideal for projects working exclusively with MySQL databases. It provides both procedural and object-oriented interfaces and directly supports MySQL-specific features.

• PDO is better suited for projects that may need to interact with multiple database types, thanks to its unified API.

Security Considerations: Both MySQLi and PDO, when used with prepared statements, protect against SQL injection attacks.

When choosing between MySQLi and PDO, consider the following:

• Use MySQLi for exclusive MySQL work and preference for a MySQL-specific API.
• Use PDO for potential multi-database support or preference for a consistent interface.

How to Connect MySQL to PHP?

First we’re going to take a look at a general steps to establish a PHP MySQL connection. And after that, we’ll check out how to PHP MySQL connect on Cloudways.

Step 1: Install Required Tools

What we’re doing: Setting up a local server environment.

What You Need:

• XAMPP (Recommended) – A local server that includes PHP, MySQL, and Apache.
• phpMyAdmin – A web-based MySQL database management tool (included with XAMPP).
• A Code Editor – Such as VS Code, Sublime Text, or Notepad++.

How to Install:

• Download XAMPP from Apache Friends.
• Install XAMPP and start Apache and MySQL from the XAMPP Control Panel.

💡 If you see green indicators, everything is running fine!

• Open your browser and go to http://localhost/phpmyadmin/ to verify MySQL is running.

Step 2: Create a MySQL Database

What we’re doing: Creating a database where our data will be stored.

Where to Do This: PHPMyAdmin

• Open your browser and go to localhost/phpmyadmin.
• Click on “New” in the left sidebar.

• Enter a database name (e.g., practice).

• Select utf8_general_ci collation (this ensures proper character encoding).
• Click “Create“.

Our database is now set up, but right now, it’s empty. We’ll have to fix that.

Step 3: Create a Project Folder

What we’re doing: Setting up a folder to store our PHP project files.

Where to Do This: XAMPP htdocs or WAMP www

• Navigate to your XAMPP or WAMP installation folder.
• Open the htdocs (for XAMPP) or www (for WAMP) folder. I’m using XAMPP, so I’ll open the htdocs folder.

• Create a new folder named practice. This is where all our project files will go.

Step 4: Create a Database Connection File

What we’re doing: Writing PHP code to connect to MySQL.

Using MySQLi:

• Inside the practice folder, create a new file named db_connection.php. Make sure the file extension is .php, not .txt. To verify, click View in the top menu and enable File name extensions (if using Windows).

• Open it in a text editor (e.g., VS Code, Sublime Text, Notepad++). I’ll use VS Code.

• Add the following code:

<?php

function OpenCon() {

$dbhost = "localhost";

$dbuser = "root";

$dbpass = ""; // Default is empty for XAMPP

$dbname = "practice";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

return $conn;

}

function CloseCon($conn) {

$conn->close();

}

?>

Notice that I’ve also added error handling, so if there’s an issue, it will display: Connection failed: error message.

Using PDO (Alternative Method):

<?php

function OpenCon()

{

$dbhost = "localhost";

$dbuser = "root";

$dbpass = ""; // Default is empty for XAMPP

$dbname = "practice";

try {

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

die("Connection failed: " . $e->getMessage());

}

return $conn;

}

function CloseCon($conn)

{

$conn = null;

}

?>

I’ve added error handling here as well, using try-catch to handle connection failures more gracefully.

Step 5: Create an Index File to Test the Connection

What we’re doing: Checking if our database connection works.

• Inside the practice folder, create a new file named index.php. Again, make sure your file is not saved as index.php.txt.
• Open it in a text editor and add the following code:

For MySQLi:

<?php

include 'db_connection.php';

$conn = OpenCon();

if ($conn instanceof mysqli) {

echo "Connected Successfully using MySQLi";

} else {

echo "Unexpected connection type.";

}

CloseCon($conn);

?>

For PDO:

<?php

include 'db_connection.php';

$conn = OpenCon();

if ($conn instanceof PDO) {

echo "Connected Successfully using PDO";

} else {

echo "Unexpected connection type.";

}

CloseCon($conn);

?>

This code we just wrote includes our database connection file and prints “Connected Successfully” if everything works.

Step 6: Test the Connection

What we’re doing: Running the PHP script in a browser.

Where to Do This: Web Browser

• Open your browser and go to: http://localhost/practice/index.php
• If everything is set up correctly, you will see: “Connected Successfully”.

• If there’s an issue, check the error message and verify your db_connection.php file settings.

Since we got the Connected Successfully message on our screen, we’ve successfully connected our database to our localhost.

If you’ve followed this guide carefully, you may have noticed that I didn’t set a password when creating the db_connection.php file.

The code is written as:

$dbpass = ""; // Default is empty for XAMPP

While this works for local development in XAMPP, it’s important to set a strong password for security reasons when deploying your project online.

So now, let’s address that…

Step 7: Secure Your Database (Optional but Recommended)

What we’re doing: Adding a password for security.

How to Do This:

1. In PHPMyAdmin, go to “User Accounts“.
2. Click “Edit Privileges” for the root user.
3. Set a strong password.
4. Update your db_connection.php file with the new password. Just like I showed you earlier, you can do this by going to C:\xampp\htdocs\practice and then replacing the old db_connection.php file with the new file.

This is the updated code with the password you can use:

<?php

function OpenCon()

{

$dbhost = "localhost";

$dbuser = "root";

$dbpass = "1234"; // Change this if you've set a password

$dbname = "practice";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

return $conn;

}

function CloseCon($conn)

{

$conn->close();

}

?>

Note: The password you set in phpMyAdmin under “Edit Privileges” for the root user must match the password in your db_connection.php file.

If they don’t match, your PHP script won’t be able to connect to MySQL, and you’ll see an authentication error like this:

Access denied for user ‘root’@’localhost’ (using password: YES)

And that is all about it. You’ve now added a password to your database connection as well.

How to Create MySQL Database on Cloudways?

To create a MySQL database in Cloudways and connect it to your PHP application, you can SSH into your server or use an FTP client to simply upload your PHP files.

For this tutorial, I’ll use the FTP approach. I’ll use FileZilla, but you can use any FTP client you like to establish a PHP database connection.

Okay, so now that you know the prerequisites, let’s get started…

Step 1: Access Your Cloudways Platform

For this tutorial, I’m assuming you have a PHP application installed on the Cloudways server already.

• Log in to your Cloudways account:
• After logging in, you’ll be taken to the Dashboard.

• Click on the View All Servers tab to access the server on which your PHP app is deployed.

• Click on the server on which your PHP app is deployed.

• In the Master Credentials tab, you can view your Public IP, Username, and Password. You’ll need these to access your server through SSH or FTP.

Step 3: Access Your Server Files via SSH or FTP

• From the Master Credentials tab, scroll down and click on Launch SSH Terminal to connect via SSH or use an FTP client like FileZilla.

• Since I’m using FileZilla, I’ll need to log in to my server using my Master Credentials.
  • For the Host, I’ll use my server’s Public IP.
  • Username – as provided in Master Credentials.
  • Password – as provided in Master Credentials.
  • Port – 22

• Once you’re inside your sever, navigate to the public_html folder, which is where the main files of your application should reside. Example path: /home/master/applications/practice/public_html/.

As you can see, there is already an index.php file present in our public_html folder.

Step 4: Create the db_connection.php File

• Now, create a new file. Name the file db_connection.php. Make sure the extension is .php. I can’t stress this enough.

• Add the following PHP code to the file:

For MySQLi (Procedural)

<?php

$servername = "your-database-host"; // Typically 'localhost' for Cloudways MySQL

$username = "your-db-username"; // Your MySQL database username (found in Cloudways)

$password = "your-db-password"; // Your MySQL database password (found in Cloudways)

$db = "your-database-name"; // The name of your database

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully";

?>

• Make sure to add the actual access details of your application to the code. So replace:
  • $servername = “your-database-host”;
  • $username = “your-db-username”;
  • $password = “your-db-password”;
  • $db = “your-database-name”;

For “servername”, use your server’s Public IP. For the rest, check out the image below for clarity.

For PDO

<?php
function OpenCon() {
    $dbhost = "your-cloudways-mysql-host";
    $dbuser = "your-db-username";
    $dbpass = "your-db-password";
    $dbname = "your-db-name";

    try {
        $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        die("Connection failed: " . $e->getMessage());
    }

    return $conn;
}

function CloseCon($conn) {
    $conn = null;
}
?>

• Now, place the file inside the public_html folder using FileZilla.

Step 5: Test the Connection

• Now, create another PHP file (e.g., index.php) in the same public_html folder to test the PHP database connection. Overwrite the existing file.
• Edit index.php and add the following code to test if the connection is working:

<?php

include 'db_connection.php'; // Include the database connection file

echo "Connected Successfully"; // If the connection works, this message will show

mysqli_close($conn); // Close the connection after use

?>

• Next, in your browser, go to your application URL. For example: http://your-app-name.cloudwaysapps.com/index.php. You can find your application URL in Access Details under Application URL.

• If everything is set up correctly, you should see: “Connected Successfully“.

And that’s it! Our PHP application is now successfully connected to our MySQL database on Cloudways. You can also use WordPress dedicated server hosting offered by Cloudways, for seamless integration with MySQL.

Host PHP Websites with Ease [Starts at $11 Credit]

• Free Staging
• Free backup
• PHP 8.3
• Unlimited Websites

TRY NOW

3 Best MySQL Management Tools in 2025

Managing a MySQL database can be challenging, but the correct tools simplify the process. Whether you are a developer, database administrator, or IT professional, a dependable MySQL management tool can conserve time and help avert costly errors. Three of the best options available are outlined below.

1. DBeaver

DBeaver is a multifaceted database management tool compatible with MySQL and various other databases. Its design facilitates a user-friendly interface while incorporating advanced features for professionals. DBeaver is commonly used in development environments and supports multiple operating systems, making it a flexible option for both teams and individuals.

Key Features:

• Advanced SQL editor with auto-completion, syntax highlighting, and error checking.
• Visual query builder for intuitive query creation.
• Data import and export capabilities supporting various formats.
• ER diagram generation for database modeling.
• Cross-platform support for Windows, macOS, and Linux.

Pros:

• Intuitive and easy-to-use interface.
• Supports multiple database types beyond MySQL.
• Regular updates and active community support.

Cons:

• Some advanced features may require a learning curve for new users.
• Performance can be affected when handling very large databases.

2. MySQL Workbench

MySQL Workbench, developed by Oracle, is a comprehensive tool for designing, developing, and managing MySQL databases. It is a preferred option for developers who require an integrated environment for database modeling, SQL development, and performance optimization. Its built-in monitoring tools allow users to manage databases efficiently and track server health.

Key Features:

• Data modeling tools for creating complex ER diagrams.
• Advanced SQL editor with features like auto-completion and syntax highlighting.
• Server administration tools for user management, backup, and configuration.
• Performance monitoring and query optimization tools.
• Cross-platform availability on Windows, macOS, and Linux.

Pros:

• Comprehensive feature set tailored for MySQL.
• Direct integration with MySQL databases ensures compatibility.
• Regular updates and support from Oracle.

Cons:

• Can be resource-intensive, leading to potential performance issues on lower-end systems.
• Some users report occasional stability issues.

3. Navicat for MySQL

Navicat for MySQL is a graphical tool designed for managing MySQL and MariaDB databases. It offers a user-friendly interface combined with advanced features, making it suitable for both beginners and experienced professionals.

Key Features:

• Data modeling and design tools for creating and visualizing database structures.
• Advanced SQL editor with features like code completion and snippets.
• Data synchronization and backup functionalities.
• Import and export capabilities supporting various file formats.
• Support for cloud databases like Amazon RDS and Google Cloud.

Pros:

• Intuitive and easy-to-use user interface.
• Efficient connection to MySQL databases via SSH and HTTP tunneling.
• Scheduling capabilities for automated tasks like backups and reports.
• Cross-platform support for Windows, macOS, and Linux.

Cons:

• Commercial software with a relatively high price point.
• Some users may find it slower compared to other tools, especially when handling large databases.

Launch Your PHP & MySQL Application on Cloudways Drupal Hosting

Host Drupal on Cloudways Managed Hosting for unbeatable performance, scalability, and ease of use!

TRY NOW

How to Troubleshoot Common MySQL Errors?

1. Incorrect Password Error

One of the most common problems occurs when the database credentials in your PHP script don’t match the actual login details.

If you see an error message like “Access denied” or “Could not connect to database”, followed by “(using password: YES)”, the issue is most likely an incorrect password.

How to Fix It:

• Double-check the database username, password, and database name in your PHP script.
• Make sure these credentials match the ones stored in your database.
• If you recently changed the password, update your PHP script accordingly.

2. Cannot Connect to MySQL Server

If you encounter an error like “Can’t connect to MySQL server on ‘server’ (110)” when using MySQLi, it indicates that the script isn’t able to reach the MySQL server.

In PDO, the error might look like this:

Connection failed: SQLSTATE[Hy000] [2002] No such file or directory

This problem often occurs when the hostname is incorrect, or the MySQL server isn’t running.

How to Fix It:

• If your database is hosted on the same server as your script, use “localhost” instead of a remote domain or IP.

• Check if the MySQL service is running. If you have SSH access, you can verify this by running:systemctl status mysql

• If your database is hosted remotely, confirm that the server firewall isn’t blocking connections.

3. Too Many Connections Error

Seeing a “Too many connections“ error means the MySQL server has reached its limit for active connections.

How to Fix It:

• Wait a few minutes and try again, as some connections may close automatically.
• Optimize your queries to reduce unnecessary connections.

If you have control over the server, increase the allowed connections by modifying your MySQL configuration file (my.cnf or my.ini):

max_connections = 500

4. Unknown Database Error

If you see “Unknown database ‘database_name’”, it means the database you’re trying to connect to doesn’t exist.

How to Fix It:

• Verify that the database name in your PHP script matches the actual database name.
• Use phpMyAdmin or the command line to check if the database exists:

SHOW DATABASES;

If the database is missing, you may need to recreate it:

CREATE DATABASE database_name;

5. Table Doesn’t Exist Error

If you get an error like “Table ‘database_name.table_name’ doesn’t exist”, it means MySQL cannot find the table you’re trying to use.

How to Fix It:

• Double-check the spelling of the table name in your queries.

• Run this command to verify if the table exists:SHOW TABLES FROM database_name;

• If the table was accidentally deleted, restore it from a backup.

6. Syntax Error in SQL Queries

If you see “You have an error in your SQL syntax”, there’s likely a mistake in your query.

How to Fix It:

• Carefully review your SQL syntax. Missing quotes, incorrect spacing, or misplaced commands can cause issues.

• Test your query in phpMyAdmin or the MySQL command line to see detailed error messages.

• If the query is built dynamically in PHP, echo it before execution to check for errors:echo $query;

7. MySQL Server Has Gone Away

If you encounter “MySQL server has gone away”, it means the connection was lost due to inactivity or a large query.

How to Fix It:

• If running long queries, increase the max_allowed_packet setting in MySQL’s configuration file (my.cnf or my.ini).

• If the server times out due to inactivity, adjust the wait_timeout setting.

8. Collation or Charset Mismatch

Errors related to collation or character sets can cause queries to fail, especially if your database and tables use different encodings.

How to Fix It:

Check the collation settings of your database and tables:

SHOW CREATE TABLE table_name;

If needed, convert the collation of your tables to match:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

9. Always Check the Error Log

If you’re stuck, your error log can provide useful clues. The log is typically found in the same folder as your script. If your script runs inside public_html, check:

Reading the error log can save time and help identify issues quickly.

Summary

Many beginners face challenges when establishing a PHP database connection with MySQL.

In this guide, I covered two methods for connecting a MySQL database to your PHP website, with clear instructions for both localhost and Cloudways server environments. I also highlighted the three best MySQL management tools to simplify database administration.

I broke down the key differences between MySQLi and PDO, so you can choose the right tool for your project. Additionally, I shared essential troubleshooting tips to resolve common connection issues.

If you have any questions, feel free to ask in the comments below.

Frequently Asked Questions