Show Menu
Cheatography
  1. Home >
  2. Programming >

MYSQLI prepared statements Cheat Sheet (DRAFT) by

How to use commonly performed MYSQLI commands with prepared statements

This is a draft cheat sheet. It is a work in progress and is not finished yet.

INSERT, UPDATE, DELETE (multiple iterat­ions)

$sql="INSERT INTO test_table (col_a, col_b) VALUES(?,?)";
$stmt=$link->prepare($sql) or die(mysqli_error($link));
$stmt->bind_param("is",$a,$b) or die(mysqli_error($link));
for($a=0;$a<10;$a++){
   $b="bob".$a;
   $stmt->execute() or die(mysqli_error($link));
   echo($stmt->insert_id."<BR>");
   echo($stmt->affected_rows."<BR>");  
}
affect­ed_rows shows number of rows added, updated or deleted. -1 if an error occurred

SELECT statements (multiple iterat­ions)

$sql="SELECT * FROM test_table WHERE col_a=?";
$stmt=$link->prepare($sql) or die(mysqli_error($link));
$stmt->bind_param("i",$a) or die(mysqli_error($link));
for($a=0;$a<10;$a++){
   $stmt->execute() or die(mysqli_error($link));
   $res=$stmt->get_result();
   echo($stmt->affected_rows."<BR>"); 
   echo($res->num_rows."<BR>");  
   while($row=$res->fetch_assoc()){
	    //returns results from query
   }
}
affect­ed_rows (from $stmt), and num_rows (from $res) give the same results here.
Remember to get_re­sult() after execute() prior to fetching the rows.

Single iteration queries (php 8.2+)

$sql= 'SELECT * FROM test_table WHERE col_a=? AND col_b=?';
$result = $link->execute_query($query, [$a,$b]);
foreach ($result as $row) {
     print_r($row); 
}
introduced in php 8.2. Combines prepare and bind into a single query.