Chat with us, powered by LiveChat

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.

How to Connect MySQL Database with PHP Websites

March 30, 2019

9 Min Read
Reading Time: 9 minutes

In the first installment of MySQL series,I introduced databases and database management system. I also presented a brief overview of the popular DBMS software, MySQL. In continuation from the previous post, I’ll be telling you how to connect MySQL Database with PHP file.

When developers usually say database, they are referring to MySQL, a highly popular DBMS that can power projects of all sizes. MySQL’s ability to handle huge volumes of data without breaking a sweat is one of its biggest selling points.

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

  • MySQL
  • MySQLi
  • PDO

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

MySQLi

It is an API that uses a connector function to link the backend of the PHP app to the MySQL database. It provides a better set of functions and extensions. It works just like the previous version, but it is more safe and faster.

PDO

PHP Data Objects (PDO) extension is a Database Abstraction Layer. It is like an interface for the backend to interact with the MySQL database and make changes without making any change in the PHP code. It also gives you the freedom to work with multiple databases. The major advantage of using PDO is that your code stays simple and portable.

In this article, I’ll discuss how to connect MySQL Database on different servers and I’ll also give you an overview of connecting Database using PDO.

  1. Connect MySQL using Localhost Server
  2. Connect MySQL using Cloudways Server
  3. Connect MySQL using PDO
  4. Connect MySQL using Remote MySQL

Host Your PHP Apps With Us For 10x Faster Performance

Don’t Let Your Customers Run Away With The Downtimes. Deploy With Us Today!

Create MySQL Database at the Localhost

First, let me tell you what PHPMyAdmin is. It’s a control panel from where you can manage the database that you’ve created. Open your browser and go to localhost/PHPMyAdmin or click “Admin” in XAMPP UI.

When you first installed XAMPP, it only created the username for it to be accessed, you now have to add a password to it by yourself. For this, you have to go to User account where the user is the same as the one shown in this picture:

change password database

Now click Edit privileges and go to Change Admin password, type your password there and save it. Remember this password as it will be used to connect to your Database.

change password database

Note: It is not necessary to change password to access databases on local host. It is a good practice and that is why we have used a password.

Create Database

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

phpmyadmin

In the new window, name your database as per your need, I am naming it “practice”. Now select Collation as utf8_general_ci, as we are using it for learning purposes and it will handle all of our queries and data that will be covered in this tutorial series. Now click on Create and your database will be created.

create database

The newly created database will be empty now, as there are no tables in it. I will be covering that in the upcoming series where we will learn how to create tables and insert data in it. In this tutorial, we are going to connect this database to a localhost using PHP.

tables in database

Create a Folder in htdocs

Now, locate the folder where you installed XAMPP and open htdocs folder (usually c:/xampp). Create a new folder inside c:/xampp/htdocs/ and name it “practice” we will place web files in this folder. Why we have created folder in htdocs? XAMPP uses folders in htdocs to execute and run your PHP sites.

Note: If your using WAMP, then add your practice folder in c:/wamp/www folder.

Create Database Connection File In PHP

Create a new php file and name it db_connnection.php and save it. Why am I creating a separate database connection file? Because if you have created multiple files in which you want to insert data or select data from the databases, you don’t need to write the code for database connection every time. You just have to include it by using PHP custom function include (include ‘connection.php’) on the top of your code and call its function and use it. It also helps when you are moving your project location from one PC to another and you have to change the values on the single file and all the changes will be applied to all the other files automatically. Write the following code in your db_connection file.

<?php

function OpenCon()
 {
 $dbhost = "localhost";
 $dbuser = "root";
 $dbpass = "1234";
 $db = "example";


 $conn = new mysqli($dbhost, $dbuser, $dbpass,$db) or die("Connect failed: %s\n". $conn -> error);

 
 return $conn;
 }
 
function CloseCon($conn)
 {
 $conn -> close();
 }
   
?>

Here is the explanation of the variable that we have used in our db_connection file:

  1. $dbhost will be the host where your server is running it is usually localhost.
  2. $dbuser will be the username i.e. root and $dbpass will be the password which is the same that you used to access your phpmyadmin.
  3. $dbname will be the name of your database which we have created in this tutorial.

Create new php file to check your database connection

Create a new php file to connect to your database. Name it index.php and add this code in this file.

<?php
include 'db_connection.php';

$conn = OpenCon();

echo "Connected Successfully";

CloseCon($conn);

?>

Run it!

Now open your browser and goto localhost/practice/index.php and you should see this screen:

connection successfully

Confirmation Message

Congratulations! You’ve successfully connected your database with your localhost! If you are not able to see this screen, then check if you have done everything right in your db_connection.php file.

Download Your RestAPI Ebook Now

Simple enter your email address and get the download link in your Inbox.

Thank You

Your Ebook is on it’s Way to Your Inbox.

Create MySQL Database at Cloudways Server

For the purpose of this tutorial, I assume that you have a PHP application installed on a web server. My setup is:

  • PHP 7.2
  • MySQL

Since I wish to focus on the topic under discussion, I decided to host my PHP application on Cloudways managed servers because I get a highly optimized hosting stack and no server management hassles. You can try out Cloudways for free by signing for an account and then following this simple GIF for setting up the server and the PHP application.

PHP Hosting: Best PHP 7 & PHP 5.6 Web Hosting

After successfully launching PHP Application on Cloudways go in the application tab and check the details of database and also you click on button to launch database manager.

Create Database Connection

The first order of the business is to set up a connection to the database. For this use the function mysql_connect. This function returns a pointer (also known as a database handle) to the database connection. This handle will be used in the code later on. Once you have the handle, remember to add your database credentials.

Create a new php file and name it db_connnection.php and save it. Why am I creating a separate database connection file? Because if you have created multiple files in which you want to insert data or select data from the databases, you don’t need to write the code for database connection every time. You just have to include it by using PHP custom function include (include ‘connection.php’) on the top of your code and call its function and use it.

At this point you have the option of either using MySQLi procedural connection query or PHP PDO based database connection:

MySQLi Procedural Query

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

?>

Connect MySQL Database with PHP Using PDO

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

   }

?>

Check Connection

<?php

include 'db_connection.php';

 

echo "Connected Successfully";

mysqli_close($conn);

?>

For PDO Close the Connection like this

$conn = null;

Give Your PHP Applications Optimum Web Performance

Host Your PHP Apps With Us & See The Blazing Web Performance Yourself!

Remote MySQL

For Remote MySQL connection Log into the Cloudways Platform with your credentials. Click on “Servers” in the top menu bar. Then, click on your target server from the list.

  • Next, go to Security menu option in the left menu, and then click the MySQL tab.
  • Add the IP address to the “Add IP to Whitelist” text area and click the “Add” button.
  • If you have multiple IP addresses, repeat the process.
  • Once you are done, click the “Save Changes” button to finalize the changes

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

Top MySQL Management tools

MySQL Workbench

MySQL Workbench is a very popular MySQL database manager for developers. It provides handy administration tools like server configuration, user management and many others.

It is available for MAC OS, Linux and Windows operating systems.

We advise all those budding developers who want to learn database administration to use it to their advantage. The management features will help them in mastering database administration in full.  

Pros

  1. It saves SQL statements
  2. Provides offline access to remote DBs
  3. Stores multiple connections in one location
  4. Visual schema and query builder available

Cons

  1. It is more complex as compared to PHPMyAdmin.
  2. Users often report software crashes.

Navicat for MySQL provides all the advanced tools that a developer require to fully manage his database operations. Its compatibility with cloud databases makes it more handy to use while managing a cloud based application. Some of its main features are:

Pros:

  1. Intuitive, easy-to-use UI.
  2. Provides easy connection to MySQL database via SSH terminal.
  3. Schedule database jobs – backup, restore, run reports and more.
  4. Import and export data from ODBC, Excel, Access, DBF, TXT, CSV, XML, JSON.
  5. Visual schema and query builders available.
  6. Compatible with Windows, Linux and MAC operating systems.
  7. Team collaboration feature available

Cons:

  1. Professional version is expensive
  2. Execution on Linux requires Wine, which slows down the IDE.
  3. Time consuming process management

MySQL Yog

This MySQL management tool has three packages available i.e Professional, Enterprise and Pro. You can choose any one of them after testing out.

The MySQL administrators can easily work with this platform and can handle their database tasks with efficiency. It is available for Windows operating system only.

PROS

  1. Smooth query designer tool available
  2. Offers advanced features that help in learning database administration.

CONS

  1. The pro version is heavy on pocket
  2. Users often report occasional software crashes
  3. Doesn’t support any databases except MySQL
  4. No native support for Linux and MAC operating system.
  5. No drag and drop support between multiple tabs.

Cloudways MySQL Database Manager

Cloudways MySQL database manager is by far one of the most handy and useful MySQL management tool for developers. It is very easy to use because of its user friendly UI and provides all the required MySQL administration tools a database manager should have:

Pros:

  1. Offers user-friendly and customizable UI with advanced features.
  2. Designs the schema tables, constraints and queries visually.
  3. Easy connection to MySQL database using SSH terminal.

Crons

  1. No drag and drop support available for multiple tabs.
  2. Lack of support for databases other than MariaDB, MySQL.

Conclusion

MySQL is the backbone of any web project. Whether its a CMS or an online task tracking platform, you need a MySQL database(s) to keep track of app and user information and server data to the application processes.

In this tutorial, we have learned two things:

  • How to create a new database
  • How to connect MySQL database with PHP

We now have our databases ready for data-entry by creating tables in the newly created databases.

If you need help in using MySQL in your projects, do leave a comment below.

In the next installment of this MySQL series, I will introduce MySQL data types. Here is a short introduction and list of major topics in this MySQL series.

Share your opinion in the comment section. COMMENT NOW

Share This Article

Launch PHP websites without the worry of Server Management.

Pre-Installed Optimized Stack with Git, Composer & SSH

Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

BFCM 2019