MySQLi - prepared queries & results

[Philip]

Okay, I've been playing around with MySQLi for a few days now - as I needed a more OOP way of reaching the DB. However, I want to be able to be given what columns to select via function parameter, and then run a prepared query like that. I've been able to do that, however I cannot for the life of me figure out how to get the results, since it you have bind the variables.

 

Here's an example of what I'm trying to do (eventually to switch to full OOP and throw this into a class):

<?php
function getRow($TABLE, $ID, $COLS) {
global $mysqli; // get mysqli

for($i=0, $col='';$i<count($COLS);$i++) {
	// loop to grab the column names (these will be safe as they are internal vars)
	if($i!=0) {
		$col.=' ,';
	}
	$col.='`'.$COLS[$i].'`';
}

// Create query:
$query = 'SELECT '.$col.' FROM `'.$TABLE.'` WHERE `id`=?';

if($dbh = $mysqli->prepare($query)) {
	// SQL is prepped -> lets add the value in now
	$dbh->bind_param("d", $ID);

	// Execute it
	$dbh->execute();

	// we can add more executions here if needed later

	// Store the result, for num_rows
	$dbh->store_result();

	if($dbh->num_rows()>0) {
		$r = ''; // to suppress any warnings from php

		// If there were rows found, then we're in business
		while($dbh->fetch() as $k => $v) {
			// return value array
			$r[$k] = $v;
		}

		// mysqli fetch array/assoc/row didn't work here either =(
		// we must find a way to bind_results with variable amounts of variables
	} else {
		// otherwise we need to display a warning.
		addError(WARNING, "No results were found");

		// return value
		$r = false;
	}

	// Free the memory
	$dbh->free_result();

	// close this connection, $mysqli will stay open still
	$dbh->close();
} else {
	// oops, error!
	addError(ERROR, "<strong>MySQLi:</strong> ".$mysqli->error);

	// return value
	$r = false;
}

return $r;
}
?>

I'll get:

Fatal error: Call to undefined method mysqli_stmt::fetch_array() in ...

 

Now, I'm thinking it is possible with variable variables (it's been a long time since I've worked with those though), however I'm not sure.

 

Any suggestions?