This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

Secure Any CMS With Ease with Our Malware Protection Add-On! LEARN MORE→

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

Updated on May 20, 2024

12 Min Read

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!

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;
?>

How to Connect MySQL to PHP (Easy Steps)

Connecting MySQL to PHP is a fundamental process for building dynamic web applications. Here is how you can connect MySQL to PHP:

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!

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.

change password database

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.

change password 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.

phpmyadmin

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.

create 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.

tables in database

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:

connection successfully

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;

php connect to mysql database

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!

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.

Q. How do I check whether MySQL is running?

A. To check to see if MySQL is running, provided it’s installed as a service, you can go to Start → Control Panel → Administrative Tools → Services and look for MySQL on that list. Check if it is running or not.

Q. How do I find my localhost port for MySQL?

A. If you use MySQL Workbench, look in the Session tab in the Information pane on the sidebar. If you use phpMyAdmin, click Home, then Variables on the top menu. Look for the port setting on the page. The set value is your MySQL port number.

Q. What is my localhost MySQL username and password?

A. Check out http://php.net/manual/en/function.mysql-connect.php. $servername = “localhost”; $username = “root”; $password = “”; This would likely work for new users, and do not forget to create the database before connecting to it.

Q. What is the standard PHP function for connecting to a MySQL database?

A. One of the reasons why PHP works so well with MySQL is the mysql_connect function that opens a database connection. This function takes five arguments.

Q. How do you connect a MySQL database with PHP?

A. You can use MySQLi or PDO extensions in PHP to establish a connection with a MySQL database, here are the steps to connect a MySQL database with PHP using MySQLi:

  • Install MySQL
  • Create a Database
  • Gather Connection Details
  • Create or open a PHP file where you’ll write the connection code.
  • Use the mysqli_connect() function to establish a connection to the MySQL database, passing the hostname, username, password, and database name as parameters.
  • Verify if the connection was successful using mysqli_connect_errno() and mysqli_connect_error() functions.
  • Close the connection using mysqli_close() when finished

Q. Can we use PHP and MySQL together?

A. Yes, PHP and MySQL can be used together. PHP is commonly used to generate dynamic content and interact with MySQL databases to store and retrieve data.

Share your opinion in the comment section. COMMENT NOW

Share This Article

Inshal Ali

Inshal is a Content Marketer at Cloudways. With background in computer science, skill of content and a whole lot of creativity, he helps business reach the sky and go beyond through content that speaks the language of their customers. Apart from work, you will see him mostly in some online games or on a football field.

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour