Last active
August 29, 2015 13:56
-
-
Save dominikwilkowski/8968451 to your computer and use it in GitHub Desktop.
Simple MySQLi abstraction layer into associative array
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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