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