Dynamic Prepared Statements
am 22.07.2009 21:48:08 von Will M--0015175cb9148163e8046f50a6df
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Hello all,
I could use some assistance with some mysqli prepared statements.
I would like to make them based on parameters passed in a function.
Currently I have a database class that I'd like to have a function for
INSERT mysqli queries.
The code I have is below.
I would like to be able to call insertInto() in a page by just inserting the
parameters of the table name,
an array with the keys as the column names and the values as the inserted
values, and then the parameter types
for the prepared statement.
Ideally the $array would be $_POST[] from a form.
This would allow much flexibility in my project.
Any ideas on how I might get this to work?
I think the main problem is having a dynamic number of possible columns to
insert into.
$this->stmt->bind_param() takes a "fixed" amount of args.
At first, I though I could use call_user_func_array, but I get a warning and
a fatal error:*
Warning*: call_user_func_array()
[function.call-user-func-array
First argument is expected to be a valid callback, 'Array' was given in *
C:\xampp\htdocs\mbpa\classes\db.php* on line *45*
*Fatal error*: Call to a member function execute() on a non-object in *
C:\xampp\htdocs\mbpa\classes\db.php* on line *47*
class Database {
function insertInto($table, $array, $param_types) {
$set_string = $this->joinKeys($array);
$query = "INSERT INTO ".$table." SET ".$set_string;
$stmt = $this->mysqli->prepare($query);
call_user_func_array(array($stmt, 'bind_param'), array($param_types,
array_values($array)));
$stmt->execute();
$stmt->close();
}
function joinKeys($array) {
foreach($array as $key => $value) {
$output[] = $key." = ?";
}
$set = implode(", ", array_values($output));
return $set;
}
}
}
$db = new Database();
$array['username'] = 'jsmith@gmail.com';
$array['password'] = md5('something');
$array['last_name'] = 'Smith';
$array['first_name'] = 'John';
$db->insertInto('users', $array, 'ssss');
--0015175cb9148163e8046f50a6df--