Ok so im new to binding, here is some code that works. I learned this format from a tutorial but i imagine there is more efficent ways to do it. In my example there is 4 names but in reality i will be doing a lot of inserts and updates in a project im working on that will have 20 or so fields. I like this approach for its clarity but obviously when your talking 20 fields or more it does take a lot of real estate. Lets look at my code first.
Here are the functions it uses:
// prepare the statement
public function query($query){
$this->stmt = $this->dbh->prepare($query);
}
public function bind($param, $value, $type = null){
if (is_null($type)) {
switch (true) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}
// run the binding process
$this->stmt->bindValue($param, $value, $type);
}
// execute the prepared statement
public function execute(){
return $this->stmt->execute();
}
and now the actual code
$database->query("
INSERT INTO users(
user_name,
user_password_hash,
user_email,
user_activation_hash
)
VALUES(
:user_name,
:user_password_hash,
:user_email,
:user_activation_hash
)
");
// bind the values
$database->bind(":user_name", "$this->user_name");
$database->bind(":user_password_hash", "$this->user_password_hash");
$database->bind(":user_email", "$this->user_email");
$database->bind(":user_activation_hash", "$this->user_activation_hash");
// execute the statement and insert the values into the database
$database->execute();
it just cries out for a loop, especially since i have a habit of calling post fields, input fields, variables and placeholders the same name, not sure if that is a good or a bad thing but i find its helpful for me when dealing with large forms which i will be.
in any case i could do something like this:
$placeholder_array = array(
"user_name" => "\$this->user_name",
"user_password_hash" => "\$this->user_password_hash",
"user_email" => "\$this->user_email",
"user_activation_hash" => "\$this->user_activation_hash"
);
// well use this copy to edit the array keys and keep original for the binding
$placeholder_copy = $placeholder_array;
// turn the array into a string i.e user_name, user_password_hash....
$fields = implode (", ", array_keys($placeholder_array));
// foreach to add : placeholder prefix for binding
foreach ($placeholder_copy as $key => $value){
$placeholder_copy [':'.$key] = $value;
unset($placeholder_copy[$key]);
}
// turn the copy array which has prefix :user_name into a string
$placeholders = implode (", ", array_keys($placeholder_copy));
$database->query("
INSERT INTO users($fields)
VALUES($placeholders)
");
// bind the values
foreach ($placeholder_copy as $bind_values => $value){
echo '$database->bind("'.$bind_values.'", "'.$value.'");' . "<br />";
}
// execute the statement and insert the values into the database
$database->execute();
i could then turn this into a function with parameters for passing in the associative array and the table name to keep my main code much cleaner.
Now imagine i am going to be doing any amount of these as the project im working on involves tons of big forms submitting data to users. I'm new to PDO and trying to grasp it so there maybe a simpler way of structuring these types of queries, i had a look on google and stackflow but i didnt really get what they were doing so i thought doing my own one would allow people to explain to me better what is going on, i would rather get this right starting my project than have to go back and change everything later. So is there a better approach or is this one ok?
Really appreciate any feedback and im glad now i took peoples advice on here and made the move to PDO.
