What is MySQLI technology in PHP?

The MySQLi(MySQL Improved) is a relational database driver used in the PHP programming language to provide an interface with MySQL databases.

There are three main API options when considering connecting to a MySQL database server:
1. PHP's MySQL Extension
2. PHP's MySQLI Extension
3. PHP Data Objects (PDO)

Following is SQL Query for test the PHP Code:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
INSERT INTO `mydb`.`users` (`id`, `first_name`, `last_name`, `email`, `type`) VALUES (NULL, 'Arun', 'kumar', 'arun.compute@domain.com', 'Admin');

The MySQLI extension supports dual interface procedural and object-oriented programming paradigm.
MySQL database connection with PHP's MySQLI driver using procedural programming.

$mysqliObj = mysqli_connect("localhost", "root", "", "mydb");
$result = mysqli_query($mysqliObj, "select * from users");
$row = mysqli_fetch_assoc($result);
print_r($row);

MySQL database connection with PHP's MySQLI driver using object-oriented programming.

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
if ($mysqliObj->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}
$result = $mysqliObj->query("SELECT * from  users");
$row = $result->fetch_assoc();
print_r($row);

Following are MySQLI Functionalities.
1. An object-oriented interface

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$result = $mysqliObj->query("SELECT * from  users");
$row = $result->fetch_assoc();
print_r($row);

2. Supports server-side Prepared Statements

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$userType = "Admin";
$stmt = $mysqliObj->prepare("SELECT * FROM users WHERE type = ?");
$stmt->bind_param("i", $userType);
$stmt->execute();
$resObj = $stmt->get_result();
while ($row = $resObj->fetch_assoc()) {
    print_r($row);echo "\n";
}

3. Supports Stored Procedures

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$mysqliObj->query("CALL p(1)");

4. Support for multiple statements

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$sql = "SELECT COUNT(*) AS _num FROM users; ";
$sql.= "INSERT INTO users(name) VALUES ('name'); ";
$sql.= "SELECT COUNT(*) AS _num FROM users; ";
if (!$mysqliObj->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqliObj->errno . ") " . $mysqliObj->error;
}

5. Support for transactions

$mysqliObj = new mysqli("localhost", "root", "", "mydb");
$sql1="INSERT INTO users (first_name, email) VALUES ('name','email@web-technology-experts-notes.in')";
$sql2="INSERT INTO users (first_name, email) VALUES ('name','email@web-technology-experts-notes.in')";
try {
  /* switch autocommit status to FALSE. Actually, it starts transaction */
  $mysqliObj->autocommit(FALSE);
  
  $res1 = $mysqliObj->query($sql1);
  if($res1 === false) {
    throw new Exception('Wrong SQL: ' . $sql1 . ' Error: ' . $mysqliObj->error);
  }
  
  $res2 = $mysqliObj->query($sql2);
  if($res2 === false) {
    throw new Exception('Wrong SQL: ' . $sql2 . ' Error: ' . $mysqliObj->error);
  } 
  $mysqliObj->commit();
  echo 'Transaction completed successfully!';
  
} catch (Exception $e) {
  
  echo 'Transaction failed: ' . $e->getMessage();
  $mysqliObj->rollback();
}

6. Enhanced debugging support
7. Embedded server support
8. API supports Charsets
9. Result set contains metadata: It have information about table along with result.

Web Technology Experts Notes

Thursday, 12 March 2015