mysql - What is the correct and easiest way to do prepared statements with PHP's mysqli? -

i have been using old mysql api in php long time , want start using mysqli both speed , security new project i'm working on. i've looked through manual , read several tutorials, i'm finding lot of conflicting , confusing information on how prepared statements in mysql.

is there in code doesn't need there, , there missing? also, easiest way simple (seems involved such simple task)?

procedural:

// build prepared statement $query = mysqli_prepare($link, "select email users id = ?");  // bind parameters statement mysqli_stmt_bind_param($query, 's', $_get['id']);  // execute statement mysqli_stmt_execute($query);  // bind variables result mysqli_stmt_bind_result($query, $email);  // print results while (mysqli_stmt_fetch($query)) {     echo $email; }  // close statement mysqli_stmt_close($query);  // close connection mysqli_close($link); 

object-oriented:

// build prepared statement $query = $link->prepare("select email users id = ?");  // bind parameters statement $query->bind_param('s', $_get['id']);  // execute statement $query->execute();  // bind variables result $query->bind_result($email);  // print results while ($query->fetch()) {     echo $email; }  // close statement $query->close();  // close connection $link->close(); 

here's guts of semi-self-explanatory class encapsulates mysqli, including prepared statements, quite tricky. it's pretty tested - i've been using year without change.

it implements prepared statements execute sql commands because change data , require nasty encoding tricks otherwise. if want selects, it's left exercise reader - it's easier. :)

<?php  class db {     var $_mysqli;     var $_result;     var $_error_msg;      public function __construct($server, $user, $password, $name)     {         $this->_mysqli = new mysqli("p:".$server, $user,                                     $password, $name);         if($this->_mysqli->connect_errno)          {             $this->_error_msg = $this->_mysqli->connect_error;         }     }      public function __destruct()     {     }      private function sql_select($sql)     {         $this->_mysqli->query("set names 'utf8'"); // little utf8 io         $this->_result = $this->_mysqli->query($sql);     }      private function sql_close()     {         $this->_mysqli->close();     }       public function errormessage()     {         return $this->_error_msg;     }      public function sqlrows($sql)     {         $rows = array();         $result = $this->sql_select($sql);         if($this->iserror())         {             return $rows;         }         while($row = $result->fetch_array())          {             $rows[] = $row;         }         $result->free();         return $rows;     }      public function sqlobjects($sql)     {         $objects = array();         $result = $this->sql_select($sql);         while($object = $this->_result->fetch_object())          {             $objects[] = $object;         }         $result->free();         return $objects;     }      public function sqloneobject($sql)     {          $result = $this->sql_select($sql);         $obj = $result->fetch_object();         $result->free();         return $obj;     }      public function sqlonerow($sql)     {         $result = $this->sql_select($sql);         if(! is_object($result))             return null;         if($result->num_rows > 0)             $row = $result->fetch_array();         else             $row = null;         $result->free();         return $row;     }      public function sqlonevalue($sql)     {         $result = $this->sql_select($sql);         if(!empty($result))         {             $row = $result->fetch_array();         }         $result->free();         return empty($row) ? null : $row[0] ;     }      // returns number of affected rows     public function sqlexecute($sql)     {         $this->_result = $this->_mysqli->query($sql);         return $this->affected_rows();     }      private function affected_rows()     {         return $this->_mysqli->affected_rows;     }      private function iserror()     {         if(empty($this->_mysqli))             return false;         return !empty($this->_mysqli->error);     }      // arguments sql , array of      // argument references (not values).     public function sqlexecuteps($sql, $args)     {         $stmt = $this->_mysqli->prepare($sql);          // make type-string         $typestr = make_typestring($args);         $params = array($typestr);         $params = array_merge($params, $args);          call_user_func_array(array($stmt, 'bind_param'), $params);         $stmt->execute();          $ret = $this->affected_rows();         $stmt->close();         return $ret;     }      public function sqlexists($sql)     {         $result = $this->sqlonerow($sql);         return !empty($result[0]);     }       function make_typestring($args)     {         assert(is_array($args));         $ret = "";         foreach($args $arg)         {             switch(gettype($arg))             {                 case "boolean":                 case "integer":                     $ret .= "i";                     break;                 case "double":                     $ret .= "d";                     break;                 case "string":                     $ret .= "s";                     break;                 case "array":                 case "object":                 case "resource":                 case "null":                 default:                     // call blob , hope                     // know you're doing.                     $ret .= "b";                     break;             }         }         return $ret;     } }   ?>