Last Updated: February 25, 2016
·
2.44K
· versatilitywerks

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');

4 Responses
Add your response

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 ·