I am trying to write a very small abstraction layer for my mysqli connection and have run into a problem. Since I am maintaining older code I need to get an associative array from my query as this is the way the code has been set up and therefor less work for me once this works... This function does work with all kinds of queries(not just select)...
my function I wrote is this:
function connectDB($query,$v=array()) {
$mysqli = new mysqli(HOST,USER,PW,DATABASE);
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") {
$fields=array();
$meta=$res->result_metadata();
while($field=$meta->fetch_field()) {
${$field->name}=null;
$fields[$field->name]=&${$field->name};
}
call_user_func_array(array($res,"bind_result"),$fields);
//return associative array
$results = array();
$i=0;
while($res->fetch()) {
$results[$i]=array();
foreach($fields as $k => $v) $results[$i][$k] = $v;
$i++;
}
}
else {
$results=$mysqli->affected_rows;
if($mysqli->affected_rows<1) $results=$mysqli->info;
}
$res->close();
}
$mysqli->close();
return $results;
}
so if I call:
$MySqlres=connectDB("select * from `modx_events` events limit 1");
var_dump($MySqlres);
I get a nice associative array with the content of my select.
Now unfortunately the following mysql query will return NULL as a value to all of it's array keys:
$MySqlres=connectDB("select *, events.`id` as `ID`,venues.`name` as `venueName`,
venues.`suburb` as `venueSuburb`,venues.`advertiser` as `venueAdvertiser`
from `modx_events` events left join `modx_venues` venues on events.`venue`=venues.`id`
where events.`id`!='e' order by events.`start_date` asc, venues.`name` limit 1");
(the same query runs as pure SQL and will return the correct values) Any idea what this could be? Does my associative array function fail? Is there something wrong with the way I implemented the query?
ps: PDO is not an option and mysqlnd is not installed... :(
ADDED QUESTION
- is this too much of overhead just to preserve the associative array return? Should I go with
$res->fetch_object()
instead?