PHP MySQL Database Functions

The more I worked with MySQL and PHP, the more I realized that I was typing the same code over and over again. And, some of the functions I used most (like mysql_real_escape_string) were really long and annoying to type. So, I created these functions as a shortcut. I include them in each file I write and they save a lot of time.

This came in really handy too when the old PHP MySQL functions were deprecated. Instead of having to update my code on thousands of pages, I just had to change it here.

<?php

////////////////////////////////////
//
// Ink Plant MySQL Helper Functions
// http://inkplant.com/code/php-mysql-database-functions.php
// version 2.1, October 2014
// See http://www.php.net/manual/en/book.mysqli.php for more info on mysqli
//
////////////////////////////////////

//set defaults & errors array
if (!array_key_exists('ignore_db_errors',$GLOBALS)) { $GLOBALS['ignore_db_errors'] = false; } //set to TRUE if you want the script to continue running even after a MySQL error
if (!array_key_exists('db_debug',$GLOBALS)) { $GLOBALS['db_debug'] = false; } //set to TRUE if you want database errors to be printed on the screen (should be set to FALSE in live environment)
if (!array_key_exists('db_connect',$GLOBALS)) { $GLOBALS['db_connect'] = true; } //set to TRUE if you want to automatically connect when this file is included
if ((!array_key_exists('db_errors',$GLOBALS)) || (!is_array($GLOBALS['db_errors']))) { $GLOBALS['db_errors'] = array(); }

$db = array(
  'hostname'=>'localhost',
  'database'=>'',
  'username'=>'',
  'password'=>''
);
$GLOBALS['accepted_log_queries_to'] = array(null);
$GLOBALS['log_queries_to'] = null;

if (!function_exists('dbConnect')) {
function dbConnect($db=false) {
	$GLOBALS['db_connect_error'] = false;
	if (!is_array($db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing!'; }
	elseif (!array_key_exists('hostname',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the hostname!'; }
	elseif (!array_key_exists('database',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the database!'; }
	elseif (!array_key_exists('username',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the username!'; }
	elseif (!array_key_exists('password',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the password!'; }
	else {
		$link = mysqli_connect($db['hostname'],$db['username'],$db['password'],$db['database']);
		if (!$link) {
			//echo '<pre>$db: '.print_r($db,true).'</pre>';
			$GLOBALS['db_connect_error'] = 'There was a problem connecting to the database.';
		}
	}
	if ($GLOBALS['db_connect_error']) {
		if (function_exists('custom_die')) { custom_die(array('error'=>$GLOBALS['db_connect_error'])); }
		else { echo '<p class="alert">'.$GLOBALS['db_connect_error'].'</p>'; die(); }
	} else {
		if ($GLOBALS['db_debug']) { $GLOBALS['db_message'] = '<p>Connected to '.$db['database'].' on '.$db['hostname'].' as '.$db['username'].' successfully.</p>'; }
		return $link;
	}
}
}

//cleans up strings and makes them database safe. turns null values into empty strings
if (!function_exists('dbEscape')) {
function dbEscape($string,$flink=null) {
	if (!$flink) { global $link; $flink = $link; }
	if (is_array($string)) { $GLOBALS['db_errors'][] = 'Array passed to dbEscape(): '.print_r($string,true); }
	$string = mysqli_real_escape_string($flink,$string);
	return $string;
}}

//includes ' where necessary and handles null values
if (!function_exists('dbPrepare')) {
function dbPrepare($string,$flink=false,$args=array()) {
	if (array_key_exists('stripslashes',$args) && $args['stripslashes']) { $string = stripslashes($string); }
	if ($string === null) {
		if (array_key_exists('not_null',$args) && $args['not_null']) { $string = ''; }
		else { return 'NULL'; }
	}
	if (is_numeric($string)) { return $string; }
	return '\''.dbEscape($string,$flink).'\'';
}}

//make a query on the database
if (!function_exists('dbQuery')) {
function dbQuery($query,$flink=false,$ignore_errors='default') {
	if (!$flink) { global $link; $flink = $link; }
	if ($ignore_errors === 'default') {
		if ($GLOBALS['ignore_db_errors']) { $ignore_errors = true; } else { $ignore_errors = false; }
	}
	$error = false;
	$result = mysqli_query($flink,$query) or ($error = mysqli_error($flink));
	if ($error) {
		if ($ignore_errors) {
			$GLOBALS['db_errors'][] = 'MySQL Database Error: '.$error.' // Query: '.$query;
		} else {
			if ($GLOBALS['db_debug']) { $error = 'MySQL Database Error: '.$error.'<br /><br />Query: '.$query; }
			else { $error = 'Database error.'; }
			if (function_exists('custom_die')) { custom_die(array('error'=>$error)); } //custom error handler function
			else { echo '<p class="alert">'.$error.'</p>'; die(); }
		}
	}
	return $result;
}}

//this function performs a query and stores it for later (great for synching two databases)
//you'll need to construct the db_queries table first for it to work
if (!function_exists('dbQueryLog')) {
function dbQueryLog($query,$flink=false,$ignore_errors='default',$log_only=false) {
	if (!$flink) { global $link; $flink = $link; }
	if (array_key_exists('log_queries_to',$GLOBALS) && ($GLOBALS['log_queries_to'])) {
		if (is_array($GLOBALS['log_queries_to'])) { $to_array = $GLOBALS['log_queries_to']; } //send to multiple databases
		else { $to_array = array($GLOBALS['log_queries_to']); } //just send to one database
	} else { $GLOBALS['log_queries_to'] = null; $to_array = array(null); }
	foreach ($to_array as $to) {
		if (array_key_exists('accepted_log_queries_to',$GLOBALS) && (is_array($GLOBALS['accepted_log_queries_to']))) {
			if (!in_array($to,$GLOBALS['accepted_log_queries_to'])) { custom_die('You are attempting to store a query for synching with an invalid destination: '.htmlspecialchars($GLOBALS['log_queries_to'])); }
		}
		dbQuery('INSERT INTO `db_queries` (`to`,`query`,`time_recorded`) VALUES ('.dbPrepare($to).','.dbPrepare($query).','.dbPrepare(time()).')',$flink,$ignore_errors);
	}
	if ($log_only) { return true; } //don't actually perform query, just store it for later
	return dbQuery($query,$flink,$ignore_errors);
}}


if (!function_exists('dbGetArray')) {
function dbGetArray($query,$flink=null,$ignore_errors=false,$mysql_assoc=true) {
	if (!$flink) { global $link; $flink = $link; }
	$result = dbQuery($query,$flink,$ignore_errors);
	$a = array();
	if ($mysql_assoc) {
		while ($row = mysqli_fetch_array($result,MYSQL_ASSOC)) {
			$a[] = $row;
		}
	} else {
		while ($row = mysqli_fetch_array($result)) {
			$a[] = $row;
		}
	}
	return $a;
}}

if (!function_exists('dbGetRow')) {
function dbGetRow($query_or_result,$flink=null,$ignore_errors=false,$mysql_assoc=true) {
	if (!$flink) { global $link; $flink = $link; }
	if (is_string($query_or_result)) {
		$query = $query_or_result;
		$result = dbQuery($query,$flink,$ignore_errors,$mysql_assoc);
	} else {
		$result = $query_or_result;
	}
	if ($mysql_assoc) { $row = mysqli_fetch_array($result,MYSQL_ASSOC); }
	else { $row = mysqli_fetch_array($result); }
	return $row;
}}

if (!function_exists('dbNumRows')) {
function dbNumRows($result) {
	$numrows = mysqli_num_rows($result);
	return $numrows;
}}

if (!function_exists('dbAffectedRows')) {
function dbAffectedRows($flink=null) {
	if (!$flink) { global $link; $flink = $link; }
	$arows = mysqli_affected_rows($flink);
	return $arows;
}}

if (!function_exists('dbInsertId')) {
function dbInsertId($flink=null) {
	if (!$flink) { global $link; $flink = $link; }
	$insert_id = mysqli_insert_id($flink);
	return $insert_id;
}}

if ($GLOBALS['db_connect']) {
	$link = dbConnect($db);
	$GLOBALS['db_connect'] = false; //so we don't try to connect again
}

unset($db); //We unset this here as a security measure so that it can't be printed out by a malicious script elsewhere. You might have to comment this out if it gives you problems, though.

?>

Comments

Loading…

This post was first published on August 22nd, 2010 and last updated on November 1st, 2014 by Robert James Reese in the following categories: Helpers, MySQL, and PHP. Before using any of the code or other content in this post, you must read and agree to our terms of use.