cougar23 Posted June 18, 2008 Share Posted June 18, 2008 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] Quote Link to comment Share on other sites More sharing options...
DarkWater Posted June 18, 2008 Share Posted June 18, 2008 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"? Quote Link to comment Share on other sites More sharing options...
cougar23 Posted June 18, 2008 Author Share Posted June 18, 2008 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); Quote Link to comment Share on other sites More sharing options...
cougar23 Posted June 18, 2008 Author Share Posted June 18, 2008 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; } Quote Link to comment Share on other sites More sharing options...
cougar23 Posted June 18, 2008 Author Share Posted June 18, 2008 anyone? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.