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', 'micheal@georama.com', 'Guide'),
(2, 'Vt', 'B2B', 'vt@georama.com', 'Guide'),
(5, 'Jana', 'Peter', 'jana@gmail.com', 'Guide'),
(6, 'Ram', 'kumar', 'ramkumar@no-spam.ws', '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 = "arun.c@no-spam.ws ";
$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();