Reputation: 1095
Best way to use MySQLi Prepared Statements in PHP
I'm new to prepared statements. I have understood the use of it and how to use it. I want to know, where to place the statements and things like that. Should I prepare one statement (or many) in each file I want to query the database? Also, can you give me an example to use prepared statements in Procedural way?
I've tried doing this. It doesn't work though.
include 'db/connect.php';
$query = "SELECT * FROM `users` WHERE `id` = ?";
$i = 5;
$statement = $connect->prepare($query);
$statement->bind_param('i', $i);
$statement->execute();
while($row = mysqli_fetch_assoc($statement)){
echo $row['email'].'<br>';
}
Upvotes: 0
Views: 1930
Answers (3)
Reputation: 1483
Prepared statements prevent SQL Injection. PHP has the awesome extension "mysqli_" which is capable of doing prepared statements.
Should I prepare one statement (or many) in each file I want to query the database? The answer is yes. You should prepare statements in each file. (If you are doing the same query in several pages save your code in one file and include them when you need)
It doesn't work though? Yeah, prepared statements usually make errors (we make errors when coding prepared statements.) So, knowing how to debug these is really useful. First of all, let's change your code like following.
include 'db/connect.php';
$query = "SELECT * FROM `users` WHERE `id` = ?";
$i = 5;
$statement = $connect->prepare($query);
if (
$statement &&
$statement->bind_param('i', $i) &&
$statement->execute()
) {
while($row = mysqli_fetch_assoc($statement)){
echo $row['email'].'<br>';
}
} else {
echo $connect -> error; // if you have an error in the query
echo $statement -> error; // if you have an error in bind_param() or execute()
}
Nice! In this way, you can easily spot any error in your query or code. And, using if statement as above will help you to prevent many errors and continue the code only if the last one was successful.
There might be much better for this. But, with several years of experimenting, I found this method as the best one. Everything about this concept is written in this article:
The Best Way to Perform MYSQLI Prepared Statements in PHP
Thanks!
Upvotes: 0
Reputation: 791
I have added the complete code to create a single method for select prepared statement and insert prepared statement, Please follow the instruction and read all the comments. create database with the name 'test' and add the following query to create "users" table in the
CREATE TABLE IF NOT EXISTS `users` (
`users_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
INSERT INTO `users` (`users_id`, `first_name`, `last_name`) VALUES
(1, 'daniel', 'martin'),
(2, 'daniel', 'martin');
<?php
error_reporting(E_ALL);
ini_set('display_errors',1);
session_start();
class mysqli_access extends mysqli{
private $ip1;
private $dbconn;
private $hostname = HST; // hostname
private $username = USR; // username
private $password = PWD; // password
private $dbname = DBN; // datbase name
function mysqli_access()
{
$ip= $_SERVER['REMOTE_ADDR'];
$ip1="ip_".str_replace('.', "", $ip);
if(!is_resource($_SESSION[$ip1]))
{
$this->dbconn = new mysqli($this->hostname,$this->username,$this->password,$this->dbname);
$_SESSION[$ip1] = $this->dbconn;
$dbconn = $this->dbconn;
if( $this->connect_error ) {
$this->Display_error('', $this->connect_errno, $this->connect_error, __FUNCTION__);
}
}
else {
$this->dbconn = $_SESSION[$ip1]; // success
}
return $this->dbconn;
}
function SelectPrepared($sql,$types,$params,$rows = '')
{
$results = array();
if ($stmt = $this->dbconn->prepare($sql)) {
if($types&&$params)
{
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++)
{
$bind_name = 'bind' . $i;
$$bind_name = $params[$i];
$bind_names[] = &$$bind_name;
}
$return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
}
$stmt->execute(); /* execute query */
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$var = $field->name;
$$var = null;
$fields_arr[$var] = &$$var;
}
call_user_func_array(array($stmt,'bind_result'),$fields_arr);
if($rows == 1){
while ($stmt->fetch()) {
$results = array();
foreach($fields_arr as $k => $v)
$results[$k] = $v;
}
}else{
$i = 0;
while ($stmt->fetch()) {
$results[$i] = array();
foreach($fields_arr as $k => $v)
$results[$i][$k] = $v;
$i++;
}
}
return $results;
}
}
public function InsertPrepared($tblName,$arrFieldNameValue,$replace_flag=0){
$TableName = $tblName;
if($replace_flag==0)
{
$sqlFirst ="INSERT INTO " . $TableName . "(";
}
if($replace_flag==1)
{
$sqlFirst ="INSERT IGNORE INTO " . $TableName . "(";
}
if($replace_flag==2)
{
$sqlFirst ="REPLACE INTO " . $TableName . "(";
}
$sqlSecond =" values(";
$params = array();
$types = '';
while(list($key,$value) = each($arrFieldNameValue))
{
$sqlFirst = $sqlFirst . $key . ",";
$sqlSecond = $sqlSecond . '?' . ",";
$params[] = $value;
$types = $types . $this->GetValType($value);
}
$sqlFirst = substr($sqlFirst,0,strlen($sqlFirst)-1) . ") ";
$sqlSecond = substr($sqlSecond,0,strlen($sqlSecond)-1) .")";
$sql = $sqlFirst . $sqlSecond;
if ($stmt = $this->dbconn->prepare($sql)) {
if($types&&$params)
{
$bind_names[] = $types;
for ($i=0; $i<count($params);$i++)
{
$bind_name = 'bind' . $i;
$$bind_name = $params[$i];
$bind_names[] = &$$bind_name;
}
$return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
}
$stmt->execute(); /* execute query */
}
return mysqli_insert_id($this->dbconn);
}
private function GetValType($Item)
{
switch (gettype($Item)) {
case 'NULL':
case 'string':
return 's';
break;
case 'integer':
return 'i';
break;
case 'blob':
return 'b';
break;
case 'double':
return 'd';
break;
}
return 's';
}
}
class Model_NAME extends mysqli_access
{
function Model_NAME() {
$this->tablename = TABLENAME;
$this->mysqli_access();
}
##---------------------------- Custom function start from here -----------------#
## fetch settings values
function getUserRow($id,$key) {
$sql ="SELECT first_name,last_name FROM ".$this->tablename." WHERE first_name=? and users_id = ?";
$param = "si";
$array_of_params[] = addslashes($key);
$array_of_params[] = addslashes($id);
$result= $this->SelectPrepared($sql,$param,$array_of_params,1);
//last parameter 1 use if want fetch single row , other wise function will return multi dimensional array
return $result;
}
## fetch settings values
function getUserRows($last_name) {
$sql ="SELECT first_name,last_name FROM ".$this->tablename." WHERE last_name= ?";
$param = "s";
$array_of_params[] = addslashes($last_name);
$result= $this->SelectPrepared($sql,$param,$array_of_params);
//last parameter 1 use if want fetch single row , other wise function will return multi dimensional array
return $result;
}
function addValue($Array) {
return $this->InsertPrepared( $this->tablename , $Array);
}
}
// configuration
define('HST','localhost');
define('USR','root');
define('PWD','techmodi');
define('DBN','test');
define('TABLENAME','users');
$obj = new Model_NAME();
$arr = array();
$arr['first_name'] = addslashes("daniel");
$arr['last_name'] = addslashes("martin");
$obj->addValue($arr); // for insert records
// after inserting get the records
$singleRow = $obj->getUserRow(1,'daniel'); // for select single records
$multiRow =$obj->getUserRows('martin'); // for select records
echo '<pre>';
echo '<br/>-------- Single Records -----------------<br/>';
print_r($singleRow);
echo '<br/>-------- Multiple Records-----------------<br/>';
print_r($multiRow);
?>
Upvotes: 1
Reputation: 1398
A procedural way... humm, been a long time since I did something procedural (strictly non OOP). I don't know why you want to limit yourself but I'll still leave PDO since it's best practice (from my POV)
Should I prepare one statement (or many) in each file I want to query the database?
I think you should prepare as many queries as you need and encapsulate your functionality so you don't have to copy paste
in your includes.php file
define a function that may be used in many locations
/**
* Get User by Id
* @returns user object or null
*/
function getUserById(PDO $connection, $id) {
$q = 'SELECT * FROM `users` WHERE `id`=?';
$stmt = $connection->prepare($q);
$stmt->execute(array($id));
return $stmt->fetchObject();
}
in your db/connect.php file
setup your PDO connection (this is my personal recomendation, I'm biased)
// do not copy paste, check the manual to see for yourself how it is done
$dsn = 'mysql:host='.$host.';dbname='.$data;
$connection = new PDO($dsn,$user,$password);
usage
whenever you need to get a user by his id just pass your database connection (PDO object) and the $id to the function
// functionality has been encapsulated and may be reused
$user = getUserById($connection,$id);
Upvotes: 1