Showing posts with label MySQLI. Show all posts
Showing posts with label MySQLI. Show all posts

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','[email protected]')"; 
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();



Thursday, 12 March 2015

What is MySQLI technology in PHP?

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', '[email protected]', '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 protected]')";
$sql2="INSERT INTO users (first_name, email) VALUES ('name','[email protected]')";
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.


Wednesday, 21 January 2015

How to use MySQLi parameterized statements?

How to use MySQLi parameterized statements?

Here I will provide code snippet for the followings
  • How to make Database Connection with PHP-MysQLI.
  • How to fetch the records from database using MysQLI parameterized/prepared statements.
  • How to insert the record in database using MysQLI parameterized/prepared statements.

Create New Table
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=1 ;

 
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `type`) VALUES
(1, 'Micheal', 'Steve', '[email protected]', 'Guide'),
(2, 'Vt', 'B2B', '[email protected]', 'Guide'),
(5, 'Jana', 'Peter', '[email protected]', 'Guide'),
(6, 'Ram', 'kumar', '[email protected]', 'Guide');


How to make Database Connection with PHP-MysQLI.
$host = 'localhost';
$user = 'root';
$password = '';
$dbName = 'arun';
$mysqliObj = new mysqli($host, $user, $password, $dbName);
if ($mysqliObj->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliObj->connect_errno . ") " . $mysqliObj->connect_error;
}



How to fetch the records from database using MysQLI parameterized/prepared statements.
$userType = "Guide";
$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['id']);echo "\n";
}


How to insert the record in database using MysQLI parameterized/prepared statements.
/** Insert into database with mysql * */
//the data
$id = null;
$firstname = "Web";
$lastname = "Technoogy";
$email = "[email protected] ";
$type = "Guide";

$stmt = $mysqliObj->stmt_init();
if (!($stmt->prepare("INSERT INTO users(first_name, last_name, email,type) VALUES (?,?,?,?)"))) {
    echo "Prepare failed: (" . $mysqliObj->errno . ") " . $mysqliObj->error;
}
$stmt->bind_param("ssss", $firstname, $lastname, $email, $type);
$stmt->execute();
$stmt->close();