Introduction

This tutorial shows database connection example in PHP and MySQL. Here we 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

Example with Source Code

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

Database Config

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

We 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 = '';
	$dbName = 'cdcol';

	// 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';
	}
	
?>

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

Please leave a comment if you have any query.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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