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.

1.   <?php
2.   
3.   ////////////////////////////////////
4.   //
5.   // Ink Plant MySQL Helper Functions
6.   // http://inkplant.com/code/php-mysql-database-functions.php
7.   // version 2.1, October 2014
8.   // See http://www.php.net/manual/en/book.mysqli.php for more info on mysqli
9.   //
10.  ////////////////////////////////////
11.  
12.  //set defaults & errors array
13.  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
14.  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)
15.  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
16.  if ((!array_key_exists('db_errors',$GLOBALS)) || (!is_array($GLOBALS['db_errors']))) { $GLOBALS['db_errors'] = array(); }
17.  
18.  $db = array(
19.    'hostname'=>'localhost',
20.    'database'=>'',
21.    'username'=>'',
22.    'password'=>''
23.  );
24.  $GLOBALS['accepted_log_queries_to'] = array(null);
25.  $GLOBALS['log_queries_to'] = null;
26.  
27.  if (!function_exists('dbConnect')) {
28.  function dbConnect($db=false) {
29.  	$GLOBALS['db_connect_error'] = false;
30.  	if (!is_array($db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing!'; }
31.  	elseif (!array_key_exists('hostname',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the hostname!'; }
32.  	elseif (!array_key_exists('database',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the database!'; }
33.  	elseif (!array_key_exists('username',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the username!'; }
34.  	elseif (!array_key_exists('password',$db)) { $GLOBALS['db_connect_error'] = 'Database info array is missing the password!'; }
35.  	else {
36.  		$link = mysqli_connect($db['hostname'],$db['username'],$db['password'],$db['database']);
37.  		if (!$link) { $GLOBALS['db_connect_error'] = 'There was a problem connecting to the database.'; }
38.  	}
39.  	if ($GLOBALS['db_connect_error']) {
40.  		if (function_exists('custom_die')) { custom_die(array('error'=>$GLOBALS['db_connect_error'])); }
41.  		else { echo '<p class="alert">'.$GLOBALS['db_connect_error'].'</p>'; die(); }
42.  	} else {
43.  		if ($GLOBALS['db_debug']) { $GLOBALS['db_message'] = '<p>Connected to '.$db['database'].' on '.$db['hostname'].' as '.$db['username'].' successfully.</p>'; }
44.  		return $link;
45.  	}
46.  }
47.  }
48.  
49.  //cleans up strings and makes them database safe. turns null values into empty strings
50.  if (!function_exists('dbEscape')) {
51.  function dbEscape($string,$flink=null) {
52.  	if (!$flink) { global $link; $flink = $link; }
53.  	if (is_array($string)) { $GLOBALS['db_errors'][] = 'Array passed to dbEscape(): '.print_r($string,true); }
54.  	$string = mysqli_real_escape_string($flink,$string);
55.  	return $string;
56.  }}
57.  
58.  //includes ' where necessary and handles null values
59.  if (!function_exists('dbPrepare')) {
60.  function dbPrepare($string,$flink=false,$args=array()) {
61.  	if (array_key_exists('stripslashes',$args) && $args['stripslashes']) { $string = stripslashes($string); }
62.  	if ($string === null) {
63.  		if (array_key_exists('not_null',$args) && $args['not_null']) { $string = ''; }
64.  		else { return 'NULL'; }
65.  	}
66.  	if (is_numeric($string)) { return $string; }
67.  	return '\''.dbEscape($string,$flink).'\'';
68.  }}
69.  
70.  //make a query on the database
71.  if (!function_exists('dbQuery')) {
72.  function dbQuery($query,$flink=false,$ignore_errors='default') {
73.  	if (!$flink) { global $link; $flink = $link; }
74.  	if ($ignore_errors === 'default') {
75.  		if ($GLOBALS['ignore_db_errors']) { $ignore_errors = true; } else { $ignore_errors = false; }
76.  	}
77.  	$error = false;
78.  	$result = mysqli_query($flink,$query) or ($error = mysqli_error($flink));
79.  	if ($error) {
80.  		if ($ignore_errors) {
81.  			$GLOBALS['db_errors'][] = 'MySQL Database Error: '.$error.' // Query: '.$query;
82.  		} else {
83.  			if ($GLOBALS['db_debug']) { $error = 'MySQL Database Error: '.$error.'<br /><br />Query: '.$query; }
84.  			else { $error = 'Database error.'; }
85.  			if (function_exists('custom_die')) { custom_die(array('error'=>$error)); } //custom error handler function
86.  			else { echo '<p class="alert">'.$error.'</p>'; die(); }
87.  		}
88.  	}
89.  	return $result;
90.  }}
91.  
92.  //this function performs a query and stores it for later (great for synching two databases)
93.  //you'll need to construct the db_queries table first for it to work
94.  if (!function_exists('dbQueryLog')) {
95.  function dbQueryLog($query,$flink=false,$ignore_errors='default',$log_only=false) {
96.  	if (!$flink) { global $link; $flink = $link; }
97.  	if (array_key_exists('log_queries_to',$GLOBALS) && ($GLOBALS['log_queries_to'])) {
98.  		if (is_array($GLOBALS['log_queries_to'])) { $to_array = $GLOBALS['log_queries_to']; } //send to multiple databases
99.  		else { $to_array = array($GLOBALS['log_queries_to']); } //just send to one database
100. 	} else { $GLOBALS['log_queries_to'] = null; $to_array = array(null); }
101. 	foreach ($to_array as $to) {
102. 		if (array_key_exists('accepted_log_queries_to',$GLOBALS) && (is_array($GLOBALS['accepted_log_queries_to']))) {
103. 			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'])); }
104. 		}
105. 		dbQuery('INSERT INTO `db_queries` (`to`,`query`,`time_recorded`) VALUES ('.dbPrepare($to).','.dbPrepare($query).','.dbPrepare(time()).')',$flink,$ignore_errors);
106. 	}
107. 	if ($log_only) { return true; } //don't actually perform query, just store it for later
108. 	return dbQuery($query,$flink,$ignore_errors);
109. }}
110. 
111. 
112. if (!function_exists('dbGetArray')) {
113. function dbGetArray($query,$flink=null,$ignore_errors=false,$mysql_assoc=true) {
114. 	if (!$flink) { global $link; $flink = $link; }
115. 	$result = dbQuery($query,$flink,$ignore_errors);
116. 	$a = array();
117. 	if ($mysql_assoc) {
118. 		while ($row = mysqli_fetch_array($result,MYSQL_ASSOC)) {
119. 			$a[] = $row;
120. 		}
121. 	} else {
122. 		while ($row = mysqli_fetch_array($result)) {
123. 			$a[] = $row;
124. 		}
125. 	}
126. 	return $a;
127. }}
128. 
129. if (!function_exists('dbGetRow')) {
130. function dbGetRow($query_or_result,$flink=null,$ignore_errors=false,$mysql_assoc=true) {
131. 	if (!$flink) { global $link; $flink = $link; }
132. 	if (is_string($query_or_result)) {
133. 		$query = $query_or_result;
134. 		$result = dbQuery($query,$flink,$ignore_errors,$mysql_assoc);
135. 	} else {
136. 		$result = $query_or_result;
137. 	}
138. 	if ($mysql_assoc) { $row = mysqli_fetch_array($result,MYSQL_ASSOC); }
139. 	else { $row = mysqli_fetch_array($result); }
140. 	return $row;
141. }}
142. 
143. if (!function_exists('dbNumRows')) {
144. function dbNumRows($result) {
145. 	$numrows = mysqli_num_rows($result);
146. 	return $numrows;
147. }}
148. 
149. if (!function_exists('dbAffectedRows')) {
150. function dbAffectedRows($flink=null) {
151. 	if (!$flink) { global $link; $flink = $link; }
152. 	$arows = mysqli_affected_rows($flink);
153. 	return $arows;
154. }}
155. 
156. if (!function_exists('dbInsertId')) {
157. function dbInsertId($flink=null) {
158. 	if (!$flink) { global $link; $flink = $link; }
159. 	$insert_id = mysqli_insert_id($flink);
160. 	return $insert_id;
161. }}
162. 
163. if ($GLOBALS['db_connect']) {
164. 	$link = dbConnect($db);
165. 	$GLOBALS['db_connect'] = false; //so we don't try to connect again
166. }
167. 
168. 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.
169. 
170. ?>

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.