generalizing a prepared statement function

[cougar23]

I'm trying to make a generalizing function to execute a MySQL prepared statement, and pass as arguments to the function the query to be executed, the bind_params data types string, the variables of the parameters to bind, and the bind_result names to bind the resulting columns to variables.  I was thinking of passing each of these as arrays, and doing some type of looping through the arrays to build the actual statement(i.e., foreach($bindParam as $param), etc).  The below code is wat I'm trying to make as a function.  Then i'd like to pass the value of $query, "sss" (or w/e data types) of bind_param, each of the parameters, and each of the bind_result fields. 

 

require_once('db_fns.php');
	if( $db = openConnection('guest', 'guest') ) {

		$query = 'SELECT m.member_id, password, member_type
					FROM member m
					WHERE m.member_id = ? and password = ? and member_type = ?';

		//build and execute query
		session_start();	
		$stmt = $db -> prepare($query);
		$stmt -> bind_param("sss", $_SESSION['validUserId'], $_SESSION['validUserPassword'], $_SESSION['validUserType']);
		$stmt -> execute();
		$stmt -> store_result();
		$stmt -> bind_result($userid, $password, $validUserType);
		$numRows = $stmt -> num_rows;		

		//do whatever is needed

		//otherwise user has permisson to access this directory 
		//close prepared statement and database connection
		$stmt -> close();
		closeConnection($db);

 

With the parameters and the result columns, is there any way to make the function generic enough to be able to handle any number of parameters/columns?  I was thinking something like, the following, if possible, but I dont know how to implement the actually doPreparedStmt() function:

 

$query = 'SELECT m.member_id, password, member_type
					FROM member m
					WHERE m.member_id = ? and password = ? and member_type = ?';

		$paramTypes = 'sss';
		$params = array($_SESSION['validUserId'], $_SESSION['validUserPassword'], $_SESSION['validUserType']);

		$resultColumns = array($userid, $password, $validUserType);

		doPreparedStmt($query, $paramTypes, $params, $resultColumns);
code]

[DarkWater]

I'd probably make this object oriented.  It'll be much easier to implement. 

 

Also, what do you mean by "able to handle any number of parameters/columns"?

[cougar23]

By a number of parameters, I mean that I want to be able to do one query like SELECT name FROM users as:

 

$query = 'SELECT name FROM users WHERE name = ?';
$paramTypes = 's';
$params = array($name);
$resultCols = array ($resultName);
doPreparedStmt($query, $paramTypes, $params, $resultCols);

 

and then be able to use the same function for a prepared stmt with two, three, four, etc parameters, such as:

 

$query = 'SELECT state FROM operations WHERE state = ? AND city = ?';
$paramTypes = 'ss';
$params = array($state, $city);
$resultCols = array ($resultState, $resultCity);
doPreparedStmt($query, $paramTypes, $params, $resultCols);

 

[cougar23]

Below is like what i had in mind, but it doesn't work:

 

function doPreparedStatement($query, $paramTypes, $params, $resultColumns) {	

	$stmt = $db -> prepare($query);

	foreach($params as $param)
		$paramString += ', '.$param;

	$stmt -> bind_param($paramTypes, $paramString);
	$stmt -> execute();
	$stmt -> store_result();

	foreach($resultColumns as $resultCol)
		$resultColString += $resultCol.', ';

	$resultColString = substr($resultColString, 0, -2);

	$stmt -> bind_result($resultColString);
	return $stmt;

}

[cougar23]

anyone?