Ultimate PHP database function for mysqli
//function to report sql errors
function errorReport($msg){
//send email to tech support with msg
}
//database connection
$db_info['host'] = 'localhost';
$db_info['user'] = 'USER';
$db_info['password'] = 'PASS';
$db_info['database'] = 'DATABASE NAME';
@ $db = new mysqli($db_info['host'],$db_info['user'],$db_info['password'],$db_info['database']);
if(mysqli_connect_errno()){
errorReport('The connection to the database could not be established.');
die('The connection to the database could not be established.');
}
//query function
function queryDB($query, $type='e'){
global $db; //get database connection
$got_it = $db->query($query); //run query
if(!$got_it){ //if error
errorReport('Page: '.$_SERVER['REQUEST_URI'].'<br>'.$db->error); } //show error
if($type == 'e' OR empty($type)){ return true; } //e = empty, or no specific return
elseif($type == 'a'){ return $got_it->fetch_array(); } //a = result array
elseif($type == 'c'){ return $db->affected_rows; } //c = changed/affected rows num
elseif($type == 'o'){ return $got_it->fetch_object(); } //o = result object
elseif($type == 'r'){ return $got_it; } //r = raw result
elseif($type == 'i'){ return $db->insert_id; } //i = inserted id
elseif($type == 'n'){ return $got_it->num_rows; } //n = num rows
}
Usage:
//get an array from table row
$grab_array = queryDB("SELECT * FROM user_accounts WHERE id='".$_SESSION['user_id']."' LIMIT 1", 'a');
//get object from table row
$grab_object = queryDB("SELECT * FROM user_accounts WHERE id='".$_SESSION['user_id']."' LIMIT 1", 'o');
//delete table row
$delete_row = queryDB("DELETE * FROM messages WHERE id='$id' LIMIT 1");
//get raw results and loop through
$get_raw_results = queryDB("SELECT * FROM messages ORDER BY date_received LIMIT 10", 'r');
while($message = mysqli_fetch_assoc($get_raw_results)){
}
//get inserted id
$inserted_id = queryDB("INSERT INTO messages SET content='hi'", 'i');
//get num rows
$num_rows = queryDB("SELECT id FROM messages WHERE recipient='blah@yahoo.com'", 'n');
//get num changed rows
$num_updated_rows = queryDB("UPDATE messages SET recipient='blah@yahoo.com'", 'c');
To Prevent SQL Injection:
//Runs a string through mysqli_real_escape
function clean($string){
return mysqli_real_escape_string($string);
}
//Escapes all POSTed values
function cleanPOST($string){
$clean = array();
foreach($_POST as $k=>$v){
$clean[$k] = escape($_POST[$k]);
}
return $clean;
}
//Escapes all URL values
function cleanGET($string){
$clean = array();
foreach($_GET as $k=>$v){
$clean[$k] = escape($_POST[$k]);
}
return $clean;
}
//Clean a POSTed value for inserting
$clean = cleanPOST();
queryDB("INSERT INTO user_accounts SET name='".$clean['name']."'", 'i');
//Clean a URL value for inserting
$clean = cleanGET();
queryDB("INSERT INTO user_accounts SET name='".$clean['name']."'", 'i');
//Clean ANY variable for inserting
queryDB("INSERT INTO user_accounts SET name='".escape($name)."'", 'i');
Written by Shane Stebner
Related protips
4 Responses
Hey, it's 2014 ;)
over 1 year ago
·
Let me guess....PDO fan? lol
over 1 year ago
·
And how would you protect this script from SQL Injection?
over 1 year ago
·
See updated pro-tip.
over 1 year ago
·
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Php
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#