Basic php crud operation to manage mysql database using pdo




<?php

class db extends PDO {
    
    public $pid = 'id'; // $pid is a primaty key of a table

    function __construct($dsn, $username, $passwd, $options = array()) {
        parent::__construct($dsn, $username, $passwd, $options);
    }

    /*
     * Function 'get_data_by_id' return single table row by an id
     * Arguments:
     * 1st:  Table name. example: 'images'
     * 2nd:  Row id to get the result, id must be a primary key of a table
     * 3rd:  Return type 'object' or 'array', Default is object
     * Returns the row if found
     * 
     *  $row = $db->get_data_by_id('images', 3, 'array');
     * 
     */
    function get_data_by_id($tablename, $id, $output_type = 'object') {
        $stmt = $this->prepare("SELECT * FROM $tablename WHERE $this->pid=?");
        $stmt->execute(array($id));
        if ($output_type == 'object') {
            $rows = $stmt->fetchAll(PDO::FETCH_OBJ);
        } else {
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        return $rows;
    }
    
    

    /*
     * Function 'get_all_datas 'return table rows by any condition
     * Arguments:
     * 1st:  Table name. example: 'images'
     * 2nd:  Take associative array of condition. example:  array('id'=>3)
     * 3rd:  Return type 'object' or 'array'. Default is object
     * Returns the rows if found
     * 
     *  $param = array(
     *     'id' => 3,
     *  );
     *  $row = $db->get_all_datas('images', $param, 'array');
     * 
     */
    function get_all_datas($tablename, $param = array(), $output_type = 'object') {
        if (!empty($param)) {
            $keys = '';
            $values_ = array();
            $i = 1;
            foreach ($param as $key => $value) {
                if (!empty($key)) {
                    ($i == 1) ? $keys = $keys . "$key=?" : $keys = $keys . "AND $key=?";
                }
                $i++;
            }
            $values_ = array_values($param);
            $stmt = $this->prepare("SELECT * FROM $tablename WHERE $keys");
            $stmt->execute($values_);
        } else {
            $stmt = $this->prepare("SELECT * FROM $tablename");
            $stmt->execute();
        }

        if ($output_type == 'object') {
            $rows = $stmt->fetchAll(PDO::FETCH_OBJ);
        } else {
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        return $rows;
    }
    

    /*
     * Function 'insert' insert values to a table by condition
     * Arguments:
     * 1st:  Table name. example: 'images'
     * 2nd:  Take associative array of values, array key must be same as table column name. example:  array('city'=> 'Dhaka', 'name'=>'Mr. Jonson').
     * Returns last inserted id of a table.
     * 
     *  $param = array(
     *     'caption' => 'Edited',
     *     'original_file_name' => 'bangladehbeautyful.png',
     *     'file_name' => '54458888beautyful.png'
     *  );
     *  $last_inserted_id = $db->insert('images', $param);
     * 
     */
    function insert($tablename, $param) {
        
        $keys = '';
        $values = '';
        $values_ = array();
        $i = 1;
        foreach ($param as $key => $value) {
            if (!empty($key)) {
                ($i == 1) ? $keys = $keys . "`$key`" : $keys = $keys . ", `$key`";
                ($i == 1) ? $values = $values . "?" : $values = $values . ", ?";
            }
            $i++;
        }
        $values_ = array_values($param);
        $sql = "INSERT INTO $tablename($keys) VALUES($values)";
        $stmt = $this->prepare($sql);
        $stmt->execute($values_);
        return $this->lastInsertId();
    }
    

    /*
     * Function 'update' update values to a table by id
     * Arguments:
     * 1st:  Table name. example: 'images'
     * 2nd:  Take an id of a table, id must be a primary key of a table.
     * 3rd:  Take associative array of values, array key must be same as table column name. example:  array('city'=>'Dhaka', 'name'=>'Mr. Jonson').
     * Returns the counter how much row updated.
     * 
     *  $param = array(
     *     'caption' => 'Edited',
     *     'original_file_name' => 'bangladehbeautyful.png',
     *     'file_name' => '54458888beautyful.png'
     *  );
     *  $row_effected = $db->update('images', 2, $param);
     * 
     */
    function update($tablename, $id, $param) {
        $keys = '';
        $values = '';
        $values_ = array();
        $i = 1;
        foreach ($param as $key => $value) {
            if (!empty($key)) {
                ($i == 1) ? $keys = $keys . "$key=?" : $keys = $keys . ", $key=?";
            }
            $i++;
        }
        $values_ = array_values($param);
        array_push($values_, $id);
        $sql = "UPDATE $tablename SET $keys WHERE $this->pid=?";
        $stmt = $this->prepare($sql);
        $stmt->execute($values_);
        return $stmt->rowCount();
    }
    
    /*
     * Function 'delete' delete the table row by id
     * Arguments:
     * 1st:  Table name. example: 'images'
     * 2nd:  Take an id of a table, id must be a primary key of a table.
     * Returns the counter how much row updated.
     * 
     *  $row_effected = $db->delete('images', 2);
     * 
     */
    function delete($tablename, $id) {
        $sql = "DELETE FROM $tablename WHERE $this->pid=:id";
        $stmt = $this->prepare($sql);
        $stmt->bindValue(':id', $id, PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->rowCount();
    }

}

$HostName = 'localhost';
$dbName = 'dbname';
$username = 'root';
$password = '';


$db = new db("mysql:host=$HostName;dbname=$dbName;charset=utf8", $username, $password);
//$db->id = 'id';

?>


Advertisements