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. ?>
Hide Line Numbers · Download



6 Comments

Sean
July 25, 2012, 10:48 am
Hi, I am new to PHP/MYSQL and your site is really helpful. Just have a question on this code. What is the function you are shortening to db_errors in your first if statement (line 14)?
Robert James Reese
July 25, 2012, 8:18 pm
That's not a function native to PHP, it's a custom error handler.
Karen
September 26, 2012, 12:46 pm
Hi Robert. Your site is awesome, thanks!! I'm not new to development but am new to connecting more complex systems like php to IIS to a mysql (or sql) db. I do this for fun so it's not my day job. I understand all the code for this script (as well as the script that initiates this one found at: http://www.inkplant.com/code/pull-twitter-feed-into-your-site.php). I have IIS 7, MySQL Server 5.5 and PHP 5.3 running on one machine on Win 7 professional. Everything seems to be talking to one another (I have run php test scripts like phpinfo to show php and iis are talking as well as a test php script that connects to the MySQL server and returns a list of db's in my browser). All works fine. I created a db and table exactly as your example and started the first script. Everything works fine as "db-functions.inc.php" gets called but only gets as far as line 26 because I get "There was a problem connecting to our database." coming up. I have MySQL running on a localhost so have that as the hostname. I tried adding in the port as it's running on port 3306 (default install) but that didn't help. Is there any more detailed code that I can add to see exactly why it can't connect? I can send more detailed info if needed (logs, etc.). I am stumped as to what might be wrong. Do I need to set special permissions in the Win 7 user accounts/permissions for the script to access the db I am connecting as root so I would think that's more than enough permissions but possibly Win 7 needs to have an account that I then set in the php.ini file.
anil
January 22, 2013, 4:26 pm
Hi, I used your dbase functions, but I get a warning on the following code:

"
if (!$link) { $GLOBALS['db_connect_error'] = true; }
mysql_select_db($db[$selected_db]['database'], $link) or ($GLOBALS['db_connect_error'] = true);
"

PHP Warning: mysql_select_db() expects parameter 2 to be resource, boolean given in /Applications […]/db-functions_inc.php on line 25

(line 25 is the second line of code).
Can you tell me what's wrong?

thx.
Klaas
September 1, 2013, 4:04 am
Very handy and nice functions!
One comment: 'the mysql_query function has been deprecated and will be removed in the future.' http://php.net/manual/en/function.mysql-query.php
To be future proof, just replace all mysql_**** for mysqli_****?
Robert James Reese
December 24, 2013, 6:06 pm
Klaas, for the most part, yes. I updated the functions above, you can copy and paste. Other than changing mysql_ to mysqli_ I also had to change the order of the $query and $link on several of them.

Leave a Comment

Name
Email
Website
Comment
Name and email are required. Your email will not be published.

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 & Conditions.