Skip to content

Instantly share code, notes, and snippets.

@dominikwilkowski
Last active August 29, 2015 13:56
Show Gist options
  • Save dominikwilkowski/8968451 to your computer and use it in GitHub Desktop.
Save dominikwilkowski/8968451 to your computer and use it in GitHub Desktop.
Simple MySQLi abstraction layer into associative array
<?php
/*****************************| MYSQLI CONNECTION FUNCTION |*****************************/
/**
* Simple MySQLi abstraction layer
*
* @param resource $mysqli The MySQLi connection link
* @param string $query The MySQL query for prepaired statement
* @param array $v The parameters to replace ? in $query. First element must be the type
* @param integer $o Option for more debug infos [0]=no infos(default) [1]=adding debug infos
*
* @return array [for select]=associative array of table result [for everything else]=associative array with affectedRows,info and insertID
*/
function connectDB($mysqli,$query,$v=array(),$o=0) {
if($mysqli->connect_errno) {
return array('info'=>array('error'=>'Connect failed: '.$mysqli->connect_error)); //error handling here
exit();
}
if(substr_count($query,"?")!=strlen($v[0]) || strlen($v[0])!=((count($v)-1)>=0 ? (count($v)-1) : 0)) {
return array('info'=>array('error'=>'Placeholders are unequal! placeholders:'.substr_count($query,"?").', replacements:'.strlen($v[0]).', param:'.(count($v)-1).' ('.$v[0].')')); //error handling here...
exit();
}
if($res=$mysqli->prepare($query)) {
//dynamically bind all $v
if($v) {
$values=array($v[0]);
for($i=1; $i<count($v); $i++) {
${'bind'.$i}=$v[$i];
$values[]=&${'bind'.$i};
}
call_user_func_array(array($res,'bind_param'),$values);
}
$res->execute();
//bind all table rows to result
if(strtolower(substr($query,0,6))=="select") {
$field=$fields=$tempRow=array();
$meta=$res->result_metadata();
while($field=$meta->fetch_field()) {
$fieldName=$field->name;
$fields[]=&$tempRow[$fieldName];
}
$meta->free_result();
call_user_func_array(array($res,"bind_result"),$fields);
//return associative array
$results=array();
$i=0;
while($res->fetch()) {
$results["res"][$i]=array();
foreach($tempRow as $k=>$v2) $results["res"][$i][$k] = $v2;
$i++;
}
$res->free_result();
}
else { //return infos about the query
$results["info"]["affectedRows"]=$mysqli->affected_rows;
$results["info"]["insertID"]=$mysqli->insert_id;
}
$res->close();
}
if($o===1) { //adding debug infos
if($mysqli->warning_count) {
if($err=$mysqli->query("SHOW WARNINGS")) {
$row=$err->fetch_row();
$results["info"]["error"].=$row[0].' ('.$row[1].'): '.$row[2];
$err->close();
}
}
$results["info"]["info"]=$mysqli->info;
$q=$query;
for($i=1;$i<count($v);$i++) $q=preg_replace("/\?/",(substr($v[0],($i-1),1)=="s" ? '"' : '').$v[$i].(substr($v[0],($i-1),1)=="s" ? '"' : ''),$q,1);
$results["info"]["query"]=$q;
$results["info"]["param"]=json_encode($v);
}
if(strtolower(substr($query,0,6))=="update" || strtolower(substr($query,0,6))=="delete") { //optimize at update or delete
preg_match_all('/((update|delete) `(.*)` )/i',$query,$tables);
foreach($tables[3] as $t) $mysqli->query('OPTIMIZE TABLE '.$t);
}
return $results;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment