PDO basic insert,view,update,delete with php function

The mysql extension is deprecated due to the some sql injunction issue. I would suggest to use mysqli or PDO. mysqli based on object oriented process (OOPS). The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. it seem how we are using mysql same for PDO.

Live demo Download

Previously i have posted PDO Form value inserting into Database topic it's related to basic insert,view,delete pdo statement.

Here i have create some basic statement php function (Basic file). Data value to post and get result. follow below function statement.

Basics.php

$dbAll -Get all value in database.

$dbRow - Get row value in database.

$DBVARS - Configuration function

$dbquery - execute query.

<?php
function __autoload($name) {
    require $name . '.php';
}
$DBVARS=array(
    
    'username'=>'root',
    'password'=>'',
    'hostname'=>'localhost',
    'db_name'=>'mostlikers'
    
    
);
function dbAll($query,$key='') {
    $q = dbQuery($query);
    $results=array();
    while($r=$q->fetch(PDO::FETCH_ASSOC))$results[]=$r;
    if(!$key)return $results;
    $arr=array();
    foreach($results as $r)$arr[$r[$key]]=$r;
    return $arr;
}
function dbInit(){
    if(isset($GLOBALS['db']))return $GLOBALS['db'];
    global $DBVARS;
    $db=new PDO('mysql:host='.$DBVARS['hostname'].';dbname='.$DBVARS['db_name'],$DBVARS['username'],$DBVARS['password']);
    $db->query('SET NAMES utf8');
    $db->num_queries=0;
    $GLOBALS['db']=$db;
    return $db;
}
function dbOne($query, $field='') {
    $r = dbRow($query);
    return $r[$field];
}
function dbLastInsertId() {
    return dbOne('select last_insert_id() as id','id');
}
function dbQuery($query){
    $db=dbInit();
    $q=$db->query($query);
    $db->num_queries++;
    return $q;
}
function dbRow($query) {
    $q = dbQuery($query);
    return $q->fetch(PDO::FETCH_ASSOC);
}
?>

Database table

Create sample data table 'user'

CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
);

PDO Insert

Post data value to pass query and execute dbQuery($sql).

$sql="INSERT INTO `user` (`name`, `email`) VALUES ('".$name."','".$email."')";
dbQuery($sql);

View - Get all record

Get all database value $dbAll

<?php
    $alluser="select * from user";
    $users=dbAll($alluser);
    foreach($users as $user) 
    {   
        echo $user['email'];  
    }                                                                               
?>

View - Get single record

Getting particular row value we need to use $dbRow

<?php
    $alluser="select * from user where id='1'";
    $users=dbRow($alluser); 
        echo $user['email'];                                                                                
?>

Update

<?php
    $alluser="UPDATE user SET name='".$name."',email='".$email."' where id='1'";
    dbQuery($alluser);                                                                              
?>

Delete

<?php
    $user="DELETE FROM `user` WHERE `id`='1'";
    dbQuery($user);                                                                             
?>

Before execute all query you need to include basic.php file. below i have written sample code.

Index.php

<html>
    <head>
    </head>
        <?php
            include_once('basic.php');
            $topics="SELECT * FROM user";
            $topic_list=dbAll($topics);  
            $one_list=dbRow($topics);  
        ?>
    <body>
        <div> 
                <h2>Show All user list</h2>
            <ul>
        <?php
            foreach ($topic_list as $key => $list) { 
        ?>  
                <li><?php echo $list['name'] ?> </li>
        <?php } ?>      
            </ul>
        </div>

        <div> 
                <h2>one topic</h2>
            <ul>
                <li><?php echo $one_list['name'] ?> </li>    
            </ul>
        </div>
    </body>
</html>

I hope this post helpful for you. more update subscribe with us.

Menu Link

03 March, 2015