php - A more efficient way of binding a big insert or update? -

ok im new binding, here code works. learned format tutorial imagine there more efficent ways it. in example there 4 names in reality doing lot of inserts , updates in project im working on have 20 or fields. approach clarity when talking 20 fields or more take lot of real estate. lets @ code first.

here functions uses:

// prepare 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 binding process $this->stmt->bindvalue($param, $value, $type); }  // execute prepared statement public function execute(){     return $this->stmt->execute(); } 

and actual code

$database->query("     insert users(         user_name,         user_password_hash,         user_email,         user_activation_hash )      values(         :user_name,         :user_password_hash,         :user_email,         :user_activation_hash     ) ");  // bind 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 statement , insert values database $database->execute(); 

it cries out loop, since have habit of calling post fields, input fields, variables , placeholders same name, not sure if or bad thing find helpful me when dealing large forms be.

in case 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" );  // use copy edit array keys , keep original binding $placeholder_copy = $placeholder_array;   // turn array string i.e user_name, user_password_hash.... $fields = implode (", ", array_keys($placeholder_array));  // foreach add : placeholder prefix binding foreach ($placeholder_copy $key => $value){ $placeholder_copy [':'.$key] = $value; unset($placeholder_copy[$key]); }  // turn copy array has prefix :user_name string  $placeholders = implode (", ", array_keys($placeholder_copy));  $database->query("     insert users($fields)     values($placeholders)  ");  // bind values foreach ($placeholder_copy $bind_values => $value){     echo '$database->bind("'.$bind_values.'", "'.$value.'");' . "<br />"; }  // execute statement , insert values database $database->execute(); 

i turn function parameters passing in associative array , table name keep main code cleaner.

now imagine going doing amount of these project im working on involves tons of big forms submitting data users. i'm new pdo , trying grasp there maybe simpler way of structuring these types of queries, had on google , stackflow didnt doing thought doing own 1 allow people explain me better going on, rather right starting project have go , change later. there better approach or 1 ok?

really appreciate feedback , im glad took peoples advice on here , made move pdo.

what i've done , continuing improve, building helper class simplify pdo based sql statements in applications.

lets take tour example. want insert data user table:

insert users(     user_name,     user_password_hash,     user_email,     user_activation_hash ) values(     :user_name,     :user_password_hash,     :user_email,     :user_activation_hash ) 

for inserting single record, sql constructed this:

function myinsertsingle($pdo, $table, $ins_array) {     $sql = "insert `".$table."` (".dbfieldlist($ins_array)             .") values (".dbvalueplist($ins_array).")"; 

...continued preparing.

• $pdo: pdo connection.
• $table: table want insert to.
• $ins_array: structured array inserting data.

for example, array $ins_array this:

$ins_array = array(     "user_name"               => "your_user",     "user_password_hash"      => "your_pw_hash",     "user_email"              => "your@mail.xyz",     "user_activation_hash"    => "your_other_hash" ); 

notice similarity array!

two functions involved. first 1 gives me list of (escaped) fieldnames.

function dbfieldlist($fields) {     $set = '';     foreach ($fields $field => $item) {         $set .= "`".$field."`,";     }        return rtrim($set, ','); } 

basically above array example, function returns

`user_name`, `user_password_hash`, `user_email`, `user_activation_hash` 

...which list of fields needed in insert-statement.

the other 1 similar values.

function dbvalueplist($fields) {     $set = '';     foreach ($fields $field => $item) {         $set .= ":".$field.",";     }        return rtrim($set, ','); } 

you guessed it, result looks this:

:user_name, :user_password_hash, :user_email, :user_activation_hash 

now have sql statement can prepare. bind values, if understand class correctly, use loop looks this:

foreach ($ins_array $field => $item) {     $pdo->bind(':'.$field,$item); } 

now can execute statement.

summary. approach single insert statement reduced this:

$ins_array = array(     "user_name"               => "your_user",     "user_password_hash"      => "your_pw_hash",     "user_email"              => "your@mail.xyz",     "user_activation_hash"    => "your_other_hash" );  myinsertsingle($pdo, 'your_table', $ins_array); 

if looks piece of overhead, keep in mind, use these functions in other sql statements select, update, delete , on.