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.

Create config.php file which holds some common database and other 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 also 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
*/

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.

require("config.php");

$sql = "SELECT * FROM table_name";
$result = dbQuery($sql);
<?php
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 *