Web Technology Experts Notes

Monday, 21 March 2016

Why use MySQLi instead of mysql?

Question: Why use MySQLI instead of MySQL?

MySQL is Not in Active Development
Officially deprecated as of PHP 5.5
Will fully removed in PHP 7.0
OOP is absent
Doesn't Support asynchronous queries
Doesn't Support Prepared statements
Doesn't Support Multiple Statements
MySQL leads to SQL injection vulnerabilities
In MySQL, we must use mysql_real_escape_string to avoid attacks where as in MySQLI its inbuilt.

Question: What is replacement of MySQL
You can use one of below:

MySQLi
PDO - PHP Database Object

Question: How to checked MySQLi is installed OR Not?

if(function_exists('mysqli_connect')){
 echo "MySQLi enabled";
}else{
 echo "MySQLi NOT enabled";
}

OR
you can check in phpinfo.

Question: How to enable MySQLi in php.ini? If not already enabled?
open php.ini file and find php_mysqli.dll
Replace

;extension=php_mysqli.dll

With

extension=php_mysqli.dll

Basically removed the colon (i.e ;)

Question: How to connect to MySQLi?
Connect to MySQLI with procedural programming

$mysqliObj = mysqli_connect("localhost", "root", "", "mydb");

Connect to MySQLI with OOP programming

$mysqliObj = new mysqli("localhost", "root", "", "mydb");

Question: What minimum PHP Version required for MySQLI?
PHP5.0

Question: Give Example of Database Connection?

$conn = new mysqli('localhost', 'root', '', 'mydb');
if ($conn->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}

Question: Give Example of Insert Record?

$sql="INSERT INTO users (name, email) VALUES ('jonny','jonny@no-spam.ws')"; 
if($conn->query($sql) === false) {
  echo "Error: ".$conn->error;
} else {
  echo "Insert Successfully";
}

Question: Give Example of Fetch Record from database?

$sql="select * from users "; 
$rs=$conn->query($sql);
 
if($rs === false) {
  echo "Error: ".$conn->error;
} else {
  echo $rs->num_rows.' Total Rows found';
}

Question: Give Example of Free the Memory after getting data?

$rs->free();

Question: Give Example of Delete Record?

$sql="delete from users where id=10"; 
if($conn->query($sql) === false) {
  echo "Error: ".$conn->error;
} else {
  echo "Insert Successfully";
}

Question: Give Syntax for Transactions in MySQLi

/*starts transaction*/
try {

  /* switch autocommit status to FALSE. Actually */
  $conn->autocommit(FALSE);
 
  $res = $conn->query($sql1);
  if($res === false) {
    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);
  }
 
  $res = $conn->query($sql2);
  if($res === false) {
    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);
  }
 
  /** You can use number of SQL Query **/
 
  $conn->commit();
  echo 'Transaction completed successfully!';
 
} catch (Exception $e) {
 
  echo 'Transaction failed: ' . $e->getMessage();
  $conn->rollback();
}
 
/* switch back autocommit status */
$conn->autocommit(TRUE);

Question: Give an example to close the MySQLi connection?