MySQL connect to database with PHP

In this article we are going to discuss about how to connect to a MySQL database in PHP. There are two separate methods to connect to  MySQL database. Previously there were 3 method to connect to the database, but the other method was deprecated few years ago due to security issues. So we are not going to discuss about the 3rd method since it’s not supported any more.

Following are the two methods which we us to connect to MySQL database.

  • Connect to MySQL using mysqli extension
  • Connect to MySQL using PDO library

Let’s see how to use these two methods to connect to the database.

Connect to MySQL using mysqli extension

Most of the time mysqli extension is pre enabled in your WAMP or LAMP server environment and no need to bother about enabling anymore using PHP ini file or using server’s PHP settings. If it has not been enabled in your server environment, please enable and restart the Apache server first to perform this operation.

Please follow below code block to build the MySQL connection using mysqli extension.

Step 01

First, create the database configuration file and save the configuration details after assigning the database configuration values in corresponding variable names. Let’s save this configuration file as ‘config.php’. In this configuration file, we are setting these variables as constant variables to use site wide.

config.php

<?php
/*
* Database Related Constants
* You'll setup your database connection details here
*/

define("DB_HOST", "localhost"); //Database Host : mostly "localhost" or as your server defined
define("DB_USER", "root"); //Database Username : As You defined
define("DB_PASS", "secureDBPW"); //Database Password : As You defined
define("DB_NAME", "icode7db"); //Database Name : As you defined

?>

Keeping a separate database configuration file is not necessary thing to do when connect to mysql database without using database configuration details everywhere. But I prefer this method to be used as a best practice in development.

As well as there are several benefits of using a separate configuration file. For an example if you needed to change the database configuration details you just need to change those details in that one configuration file. You don’t need to edit each an every page where database connection exists. So my advice is to use this method in your development.

Step 02

We have completed implementing our configuration file and now we are ready to implement the code block for connecting to the database.  In this code block I’m going to use Object Oriented developing method. Since PHP is supported to use in procedural way, we can use that method too. But as a best practice always try to implement your web applications in Object Oriented way. It will add an additional value to your code.

database.php

<?php


class Database
{
	public $conn = null; //database connection variable
	
	/*
	*@ param string Database Host
	*@ param string Database Username
	*@ param string Database Password
	*@ param string Database Name
	*/
	function __construct($DB_HOST="", $DB_USER="", $DB_PASS="", $DB_NAME="")
	{
		
		//prepare connection data
		//In this scenario, developer can pass another connection detail as arguments to the function
		//and build a separate connection to an another database which is separate than the details
		//which exists in database configuration file.
		$conn_data = $this->prepare_conn_data($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
		$connection = mysqli_connect($conn_data["DB_HOST"], $conn_data["DB_USER"], $conn_data["DB_PASS"], $conn_data["DB_NAME"]);
		
		if($connection)
		{
			//successfully created the database connection
			$this->conn = $connection;
		}
		else
		{
			//Database connection failed due to some error. 
			//Do the needful to show this error message in the application in properly.
			
			echo "Could connect to database. Please check database configuration details.";
		}
	}
	
	/*
	*@ param string Database Host
	*@ param string Database Username
	*@ param string Database Password
	*@ param string Database Name
	*/
	function prepare_conn_data($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME)
	{
		$data = array();
		
		//get connection details from the default configuration file 
		//if connection details has not been passed in the function parameters.
		//assume config.php file has been included in this code block
		if($DB_HOST == "")
		{
			$data["DB_HOST"]= DB_HOST; //constant variable from database configuration file
		}
		else
		{
			$data["DB_HOST"]= $DB_HOST;
		}
		
		if($DB_USER == "")
		{
			$data["DB_USER"]= DB_USER; //constant variable from database configuration file
		}
		else
		{
			$data["DB_USER"]= $DB_USER;
		}
		
		if($DB_PASS == "")
		{
			$data["DB_PASS"]= DB_PASS; //constant variable from database configuration file
		}
		else
		{
			$data["DB_PASS"]= $DB_PASS;
		}
		
		if($DB_NAME == "")
		{
			$data["DB_NAME"]= DB_NAME; //constant variable from database configuration file
		}
		else
		{
			$data["DB_NAME"]= $DB_NAME;
		}
		
		return $data;
	}
	
	function __destruct()
	{
		//This function is automatically called when the link to this class is delinked
		//That means database connection is needed no more.
		//So we need to close the connection to the database, unless it will be an issue for
		//concurrent access limitation to the database
		
		mysqli_close($this->conn);
	}
}

?>

Database class creating part is over and now we can check if we have implemented it correctly in next step.

Step 03

Now we have implemented our mysql database connection class using mysqli extension. Now it’s ready to use for our development.

Let’s try this out with a sample database.

test.php

<?php

//include required files in the script
require_once("./config.php"); //database configuration file
require_once("./database.php");//database class file

//create the database connection
$db = new Database();

//run a query using created database connection
//assume there is a table named like 'test' which is having two fields
//like 'id' and 'name'. Let's retrieve data from table
$sql = "SELECT * FROM test";

$results = $db->conn->query($sql);

//check for number of records
if($results->num_rows > 0)
{
	//reading data as array elements
	while($result = $results->fetch_assoc())
	{
		echo "Id: " . $result["id"]. " - Name: " . $result["name"]."
";
	}
}
else
{
	echo "Records not found.";
}

?>

This code is included in source code which is available for download. You can build your own library for database connection by following this code.

 

Connect to MySQL using PDO library

PDO is library which comes in PHP language and but it should be enabled manually since this is disabled in php.ini file by default. If you are working on an online server, then it should be recompiled with php and restart the Apache server to enable this PDO library. Once it has been enabled, double check if it has been enabled using phpinfo function in PHP. There you can search for PDO and if it has been enabled then it will show the status.

Follow below code blocks to connect to MySQL database using PDO library.

 

Step 01

This step is same as in previous section, keep the database configuration variables in a separate file. It will make it easier to handle the application when changes comes of database configuration details.

config.php

<?php
/*
* Database Related Constants
* You'll setup your database connection details here
*/

define("DB_TYPE", "mysql"); // DB_TYPE - Database Language : mysql/mssql/pgsql etc...
define("DB_HOST", "localhost"); // DB_HOST - Database Host : mostly "localhost" or as your server defined
define("DB_USER", "root"); // DB_USER - Database Username : As You defined
define("DB_PASS", "secureDBPW"); // DB_PASS - Database Password : As You defined
define("DB_NAME", "icode7db"); // DB_NAME - Database Name : As you defined
define("DB_CHARSET", "utf8"); // DB_CHARSET - Database Password : As You defined
?>

 

Step 02

Now we are about to implement the database connection class as our next step. Same as in previous step, we are going to implement this using Object Oriented development style. As a best practice I always prefer to use Object Oriented style for your developments.

database.php

<?php
class Database
{
	public $conn = null; //database connection variable
	
	/*
	*@ param string Database Type
	*@ param string Database Host
	*@ param string Database Username
	*@ param string Database Password
	*@ param string Database Name
	*@ param string Database Database charset
	*/
	function __construct($DB_TYPE="", $DB_HOST="", $DB_USER="", $DB_PASS="", $DB_NAME="", $DB_CHARSET="")
	{
		//prepare connection data
		//In this scenario, developer can pass another connection detail as arguments to the function
		//and build a separate connection to an another database which is separate than the details
		//which exists in database configuration file.
		$conn_data = $this->prepare_conn_data($DB_TYPE, $DB_HOST, $DB_USER, $DB_PASS, $DB_NAME, $DB_CHARSET);
		try
		{
			//create the connection to the database
			$conn = new PDO($conn_data["DB_TYPE"].':host='.$conn_data["DB_HOST"].';dbname='.$conn_data["DB_NAME"].";charset=".$conn_data["DB_CHARSET"], $conn_data["DB_USER"], $conn_data["DB_PASS"]);
			
			//set the PDO error mode to exception
    		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			$this->conn = $conn;
		}
		catch(PDOException $e)
		{
			//Database connection failed due to some error. 
			//Do the needful to show this error message in the application in properly.
			
			echo "Could connect to database. Please check database configuration details.";
			exit();
		}
	}
	
	/*
	*@ param string Database Type
	*@ param string Database Host
	*@ param string Database Username
	*@ param string Database Password
	*@ param string Database Name
	*@ param string Database Database charset
	*/
	function prepare_conn_data($DB_TYPE, $DB_HOST, $DB_USER, $DB_PASS, $DB_NAME, $DB_CHARSET)
	{
		$data = array();
		
		//get connection details from the default configuration file 
		//if connection details has not been passed in the function parameters.
		//assume config.php file has been included in this code block
		if($DB_TYPE == "")
		{
			$data["DB_TYPE"]= DB_TYPE; //constant variable from database configuration file
		}
		else
		{
			$data["DB_TYPE"]= $DB_TYPE;
		}
		
		if($DB_HOST == "")
		{
			$data["DB_HOST"]= DB_HOST; //constant variable from database configuration file
		}
		else
		{
			$data["DB_HOST"]= $DB_HOST;
		}
		
		if($DB_USER == "")
		{
			$data["DB_USER"]= DB_USER; //constant variable from database configuration file
		}
		else
		{
			$data["DB_USER"]= $DB_USER;
		}
		
		if($DB_PASS == "")
		{
			$data["DB_PASS"]= DB_PASS; //constant variable from database configuration file
		}
		else
		{
			$data["DB_PASS"]= $DB_PASS;
		}
		
		if($DB_NAME == "")
		{
			$data["DB_NAME"]= DB_NAME; //constant variable from database configuration file
		}
		else
		{
			$data["DB_NAME"]= $DB_NAME;
		}
		
		if($DB_CHARSET == "")
		{
			$data["DB_CHARSET"]= DB_CHARSET; //constant variable from database configuration file
		}
		else
		{
			$data["DB_CHARSET"]= $DB_CHARSET;
		}
		
		return $data;
	}
	
	function __destruct()
	{
		//This function is automatically triggered when the link to this class is delinked
		//That means database connection is needed no more.
		//So we need to close the connection to the database, unless it will be an issue for
		//concurrent access limitation to the database
		
		$this->conn = null;
	}
}
?>

Database connection is prepared now and for more details of the code block, I have commented them in necessary parts of the code.

 

Step 03

We can test this to check if we have implemented our code to connect to the database.

test.php

<?php
//include required files in the script
require_once("./config.php"); //database configuration file
require_once("./database.php");//database class file

//create the database connection
$db = new Database();

//run a query using created database connection
//assume there is a table named like 'test' which is having two fields
//like 'id' and 'name'. Let's retrieve data from table
$sql = "SELECT * FROM test";

$stmt = $db->conn->prepare($sql);
$execute = $stmt->execute();

if($execute)
{
	$results=$stmt->fetchAll();
	
	//check for number of records
	if(is_array($results) && count($results)>0)
	{
		//reading data as array elements
		foreach($results as $result)
		{
			echo "Id: " . $result["id"]. " - Name: " . $result["name"]."
";
		}
	}
	else
	{
		echo "Records not found.";
	}
}
else
{
	echo "MySQL query executaion failed.";
}
?>

This code block also included in source code file which is available for download.

Now you are well prepared to build a database connection and you are able to connect to a MySQL database using two methods now. using PHP and MySQL. Wish you all the best with your future developments.

If you have questions or suggestions, then please put them in the comment section. Your comments and suggestions are always welcome.

You can download the full source code in a zipped file using below link.

Download Source Code

Leave a Reply

Your email address will not be published. Required fields are marked *