PHP MySqli Prepared statements - Object oriented style

PHP MySqli Prepared Statements - Object oriented style

mysqli::stmt_init() Initializes a statement and returns an object for use with mysqli_stmt_prepare
mysqli::prepare( $mysqli-link ) Prepare an SQL statement for execution
mysqli_stmt::bind_param( 'bind-params', $var ) Binds variables to a prepared statement as parameters. Bind parameters: s - string, b - blob, i - int, d - double
mysqli_stmt::execute() Executes a prepared Query
$mysqli_stmt::errno Returns the error code for the most recent statement call
$mysqli_stmt::affected_rows Returns the total number of rows changed, deleted, or inserted by the last executed statement
mysqli_stmt::close() Closes a prepared statement

  //... connect to database
  @ $conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

  if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
  }

 ... SELECT

   //... connect to database

   $stmt = $conn->prepare("SELECT email FROM users WHERE id= ? LIMIT 1 ");
   $stmt -> bind_param('i', $id);
   $stmt -> execute();
   $stmt -> store_result();
   $stmt -> bind_result($email);
   $stmt -> fetch();

echo $email;
   $stmt->close();

... INSERT
short version

   //... connect to database

   $stmt = $conn->prepare("INSERT users (username,password,email) VALUES (?, ?, ?)");
   $stmt->bind_param("sss", $username,$password,$email);
   $stmt->execute();
   $stmt->close();

long version

   //... connect to database

   if ($stmt = $conn->prepare("INSERT users (username,password,email) VALUES (?, ?, ?)"))  {
        $stmt->bind_param("sss", $username,$password,$email);
        $stmt->execute();

        // if record exist
        if ($stmt->errno  == 1062) {
           echo "Duplicate! record";  // or insert javascript pop up
           //   or  
           // if($stmt->affected_rows != 1) {   //  if record is add then return=1 if is not add then  return is  -1
           //  echo  "oopsss record not add into database... ";
           $stmt->close();
        }  else {
           echo $stmt->affected_rows.' records inserted into database.';
           $stmt->close();
        }
   } else {
      echo "ERROR: Could not prepare SQL statement.";
   }

$mysqli->close();

...UPDATE
short version

  //... connect to database

  $stmt = $conn->prepare("UPDATE users SET username=?, password=?, email=? WHERE id=?");

   $stmt->bind_param("sssi", $username,$password,$email,$id);
   $stmt->execute();

   echo $stmt -> affected_rows;
   $stmt->close();

long version

//... connect to database

if ($stmt = $conn->prepare("UPDATE users SET username=?, password=?, email=? WHERE id=?")) {

   $stmt->bind_param("sssi", $username,$password,$email,$id);
   $stmt->execute();

   if($stmt->affected_rows != 1) {
       echo  "oopsss record not add into database... ";
       $stmt->close();
   } else {
      echo $stmt->affected_rows.' records inserted into database.';
      $stmt->close();
   }
} else {  // show an error if the query has an error
    //echo "ERROR: Could not prepare SQL statement.";
    echo "<script type=\"text/javascript\">
             function error_popup(){
             document.getElementById('erroradd').style.display='none';}
             setTimeout('error_popup();', 5000);
             </script>
             <div id=\"erroradd\">ERROR: Could not prepare SQL statement.</div>";
             $conn->close();
}

... DELETE

 //... connect to database
    
  // Prepare a DELETE statement   
  $stmt = $conn->prepare("DELETE FROM usertable WHERE id= ?");

  $stmt->bind_param("i", $id);
  $stmt->execute();

  echo $stmt -> affected_rows;

  $stmt->close();

Links:
MySQL and PHP - Object oriented style
MySQL and PHP - Procedural Style
MySQL Operators
MySQL Data Types
PHP MySQL Improved Extension
mysqli::prepare(), mysqli_stmt::bind_param(), mysqli_stmt::execute(), $mysqli_stmt::errno, $mysqli_stmt::affected_rows, mysqli_stmt::close() - php.net