mysqli - Cheat List

mysqli-cheal-list

//Resource: http://www.pontikis.net/blog/how-to-use-php-improved-mysqli-extension-and-why-you-should

/*MySQLi supports two tpe of APIs
1. Procedural (NOT RECOMMENDED)
2. Object Oriented API
*/
/********************connecting to database******************************/
//Procedural
$conn = mysqli_connect($dbServer,$database, $username, $password);
if(mysqli_connect_errno()){
  echo mysqli_connect_error(); exit();
}





//Object Oriented Way (RECOMMENDED)
$conn = new mysqli($dbServer,$username, $password, $database);
if($conn->connect_errno > 0){
echo $db->connect_error; exit();
}


#####################SELECT###############
$sql = "SELECT col1, col2,col3 from table_name;"
$rs = $conn->query($sql);

if($rs === false){
  echo $conn->error;
}

//column names (RECOMMENDED)
while($row = $rs->fetch_assoc()){
  echo $row['col1']."<br>";
  echo $row['col2']."<br>";
  echo $row['col3']."<br>";
}

//column number (NOT RECOMMENDED)
$rs = $conn->query($sql);
while($row = $rs->fetch_row()){
  echo $row[0]."<br />";
  echo $row[1]."<br />";
  echo $row[2]."<br />";
}
$rs->free(); //Optional: It is advisable to free result when you've finished playing with result set

//store resultset to Associative array
$rs = $conn->query($sql);
$arr_assoc = $rs->fetch_all('MYSQLI_ASSOC');
foreach($arr_assoc as $row){
echo $row['col1']."<br />";
echo $row['col2']."<br />";
echo $row['col3']."<br />";
}
$rs->free(); //Optional: It is advisable to free result when you've finished playing with result set

//store resultset to Index (numeric) array
$rs = $conn->query($sql);
$arr_index = $rs->fetch_all('MYSQLI_NUM');
foreach($arr_index as $row){
  echo $row[0]."<br />";
  echo $row[1]."<br />";
  echo $row[2]."<br />";
}
$rs->free(); //Optional: It is advisable to free result when you've finished playing with result set

//store resultset to Both Associative & Index array
$rs = $conn->query($sql);
$arr = $rs->fetch_all('MYSQLI_BOTH');
foreach($arr as $row);
{
  echo $row['col1']."<br />"; //echo $row[0];
  echo $row['col2']."<br />"; //echo $row[1];
  echo $row['col3']."<br />"; //echo $row[4];
}
$rs->free(); //Optional: It is advisable to free result when you've finished playing with result set


#############################################INSERT#############################################
//real_escape_string is used to escape special characters NULL,\n,\r,\,'," and control-z in string before storing it in database. (Mainly to prevent sql injection)

$v1 = $conn->real_escape_string($col1_val);
$v2 = $conn->real_escape_string($col2_val);
$v3 = $conn->real_escape_string($col3_val);

$sql = "INSERT INTO table_name (col1,col2,col3) VALUES ('".$v1."','".$v2."', '".$v3."')";
$inert = $conn->query($sql);
if($insert === FALSE) {
  echo $conn->error;
}else{
  echo 'LAst Insert ID:'.$conn->insert_id;
  echo $conn->affected_rows;
}

#############################################UPDATE#############################################

$sql = "UPDATE table_name SET col1='val1', col2='val2', col3='val3' where id = '1' ";
$update = $conn->query($sql);
if($update === FALSE){
echo $conn->error;
} else {
  echo $conn->affacted_rows;
}

#####################################################DELETE###################################

$sql = "DELET FROM table_name where id = 1";
$delete = $conn->query($sql);
if($delete === FALSE){
echo $conn->error;
} else {
echo $conn->affacted_rows;
}

###############################################CLOSE CONNECTION##################################
$conn->close(); //Optional: Close connection once you have finished playing with DB