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();


4 comments :

Sai Elakiyaa said...

A very interesting topic that you have discussed here, definitely, your tips help me out to reach my passion. I would love to see more updates. Thank you, admin.
Selenium training institute in Chennai
Selenium Training Chennai

Sakthi Murugan said...

This is exactly what I wanted to read, hope in future you will continue sharing such an excellent article.
Java training institutes in chennai
java courses

Sumaya Manzoor said...

Generally, I don't make comments on sites, however, I need to say that this post really pushed me to do as such thing.
big data training institute in chennai
big data classes in chennai

Sai Elakiyaa said...

I think this is the best article today about the future technology. Thanks for taking your own time to discuss this topic, I feel happy about that curiosity has increased to learn more about this topic. Keep sharing your information regularly for my future reference. Cloud Computing Courses in Chennai
Cloud Computing Certification in Chennai

Post a Comment