Monday 21 March 2016

Why use MySQLi instead of mysql?

Why use MySQLi instead of mysql?

Question: Why use MySQLI instead of MySQL?
  1. MySQL is Not in Active Development
  2. Officially deprecated as of PHP 5.5
  3. Will fully removed in PHP 7.0
  4. OOP is absent
  5. Doesn't Support asynchronous queries
  6. Doesn't Support Prepared statements
  7. Doesn't Support Multiple Statements
  8. MySQL leads to SQL injection vulnerabilities
  9. 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:
  1. MySQLi
  2. 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?
$conn->close();