Introduction

This tutorial shows database connection example in PHP and MySQL. Here I will create a PHP file with commonly used functions on how to connect to MySQL database using PHP. It is mandatory to establish database connection when you are working on a dynamic application. The database may be any one of your choice or according to the requirement of your application. Here I am going to use MySQL database to serve the purpose.

I have used here mysqli_* function to show a database connection example in PHP and MySQL. I have also written here some most commonly used functions to reduce the efforts.

Prerequisites

Knowledge of PHP and MySQL

Project Directory

Let’s create a project root directory called php_db_conn. So I will put all PHP files under this project’s root directory.

Database Config

Create config.php file which holds some database and other common configurations parameters.

I have also turn on the error reporting here but this should be only in development environment and in production environment you must turn it off.

The below file configures some commonly used variables such as database connection parameters, document root of the application, web root of the application and server root of the application. As these variables may be required to use anywhere in the application so it is better to configure them at one place and use them uniformly throughout the application wherever required.

<?php

	ini_set('display_errors', 'On');

	error_reporting(E_ALL);

	//database connection config
	$dbHost = 'localhost';
	$dbUser = 'root';
	$dbPass = 'root';
	$dbName = 'roytuts';

	// setting up the web root and server root
	$thisFile = str_replace('\\', '/', __FILE__);
	$docRoot = $_SERVER['DOCUMENT_ROOT'];

	$webRoot = str_replace(array($docRoot, 'config.php'), '', $thisFile);
	$srvRoot = str_replace('config.php', '', $thisFile);

	define('WEB_ROOT', $webRoot);
	define('SRV_ROOT', $srvRoot);

	require_once 'database.php';

/*
* End of file config.php
*/

Database Connection

Most commonly used function are put in a database.php file.

The below php file is created in order to establish database connection and provide common functions ready to use for querying database without repeating the same code.

The first line of the below source code shows database connection example in PHP and MySQL.

<?php

	$dbConn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName) or die('MySQL connect failed. ' . mysqli_connect_error());
	
	function dbQuery($sql) {
		global $dbConn;
		$result = mysqli_query($dbConn, $sql) or die(mysqli_error($dbConn));
		return $result;
	}
	
	function dbAffectedRows() {
		global $dbConn;
		return mysqli_affected_rows($dbConn);
	}
	
	function dbFetchArray($result, $resultType = MYSQLI_NUM) {
		return mysqli_fetch_array($result, $resultType);
	}
	
	function dbFetchAssoc($result) {
		return mysqli_fetch_assoc($result);
	}
	
	function dbFetchRow($result) {
		return mysqli_fetch_row($result);
	}
	
	function dbFreeResult($result) {
		return mysqli_free_result($result);
	}
	
	function dbNumRows($result) {
		return mysqli_num_rows($result);
	}
	
	function dbNumFields($result) {
		return mysqli_num_fields($result);
	}
	
	function dbInsertId() {
		global $dbConn;
		return mysqli_insert_id($dbConn);
	}

	function closeConn() {
		global $dbConn;
		mysqli_close($dbConn);
	}
/*
* End of file database.php
*/

Usage Example

Here are very few usage examples of the above ready made functions to query the database and working on the query results.

<?php

	require("config.php");

	$sql = "SELECT * FROM table_name";
	$result = dbQuery($sql);

	if (dbNumRows($result) > 0) {
		while ($row = dbFetchAssoc($result)) {
			echo $row['field1'];
			echo $row['field2'];
		}
	} else {
		echo 'No result found';
	}
	
?>

Testing PHP MySQL Connection

Let’s say you have the following user table in the database roytuts.

And you want to fetch all records from the above table using PHP program. So you could write the following code in the PHP file called example.php:

<?php

	require("config.php");

	$sql = "SELECT * FROM user";
	$result = dbQuery($sql);

	if (dbNumRows($result) > 0) {
		while ($row = dbFetchAssoc($result)) {
			echo "\n";
			echo 'Id: ' . $row['id'] . "\n";
			echo 'Name: ' . $row['name'] . "\n";
			echo 'Email: ' . $row['email'] . "\n";
			echo 'Phone: ' . $row['phone'] . "\n";
			echo 'Address: ' . $row['address'] . "\n";
			echo "\n";
		}
	} else {
		echo 'No result found';
	}
	
?>

When you run the above file using command: php example.php using CLI then you will see the following output:

Hope you have understood database connection example in PHP and MySQL.

Source Code

Download