PHP PDO CRUD

PDO connection to db. CRUD

<?php
//PDO connection to db. CRUD
class pdoCRUD
{
    //database info
    const DB_NAME="test";
    const DB_USERNAME="root";
    const DB_PASSWORD="";
    const DB_HOST="localhost";
	
    // db username/pass stored in constants
    private $db;
    private $username=self::DB_USERNAME;
    private $password=self::DB_PASSWORD;
    private $dsn;
	
    //Connect to the database and set the error mode to Exception
    //Throws PDOException on failure
    public function conn()
    {
        if (!$this->db instanceof PDO)
        {
            $this->dsn='mysql:dbname=' . self::DB_NAME . ';host=' . self::DB_HOST;
            $this->db = new PDO($this->dsn, $this->username, $this->password, 
                array(PDO::ATTR_PERSISTENT => true));
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
    }

	public function rawQuery($query)
	{
		try {
			$this->conn();
			return $this->db->exec($query);
		}catch(PDOException $e) {
			echo $e->getMessage();
		}
	}
	//param string $query the query
    //param string $var the bind variables
    //return array on success or throw PDOException on failure
    public function selectData($query, $var=null)
    {
		try{
			$this->conn();
			$stmt = $this->db->prepare($query);
			$stmt->execute($var);
			return $stmt->fetchAll(PDO::FETCH_ASSOC);
		}catch(PDOException $e) {
			echo $e->getMessage();
		}
    }


    //param string $query the query
    //param string $var the bind variables
    //return success or throw PDOException on failure
    public function updateData($query, $var=null)
    {
		try{
			$this->conn();
			$stmt = $this->db->prepare($query);
			return $stmt->execute($var);
		}catch(PDOException $e) {
			echo $e->getMessage();
		}
    }
	
	//param string $query the query
    //param string $var the bind variables
    //return success or throw PDOException on failure
    public function deleteData($query, $var=null)
    {
		try{
			$this->conn();
			$stmt = $this->db->prepare($query);
			return $stmt->execute($var);
		}catch(PDOException $e) {
			echo $e->getMessage();
		}
    }


    //param string $table
    //param array $values
    //return int The last Insert Id on success or throw PDOexeption on failure
    public function insertData($table, $fieldnames, $values)
    {
        $sql = "INSERT INTO $table";
        //set the field names
        $fields = '( ' . implode(' ,', $fieldnames) . ' )';

        //setup the placeholders - making the long "(?, ?, ?)..." string
        $rowPlaces = '(' . implode(', ', array_fill(0, count($fieldnames), '?')) . ')';
        $allPlaces = implode(', ', array_fill(0, count($values), $rowPlaces));

        //put the query together 
        $sql .= $fields.' VALUES '.$allPlaces;

        //put the insert values together
        $insert_values = array();
        foreach($values as $v){
            $insert_values = array_merge($insert_values, array_values($v));
        }
		
		try{
			$this->conn();
			$stmt = $this->db->prepare($sql);
			return $stmt->execute($insert_values);
		}catch(PDOException $e) {
			echo $e->getMessage();
		}
    }
}

Using the class above to perform create, insert, select, update, delete queries.

//create the pdoCRUD helper
$pdo_obj = new pdoCRUD();


//create table
$createCustomersTable = 
"CREATE TABLE customers 
(
pid INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(pid),
first_name CHAR(15),
last_name CHAR(15),
email CHAR(50),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
echo $pdo_obj->rawQuery($createCustomersTable). " rows affected, table created!\n";


//insert data
$fieldNames=array('first_name','last_name','email','age');
$custData=array();
$cust1=array('John','Doe','john@gmail.com','25');
$cust2=array('Ada','Lin','ada@gmail.com','22');
$cust3=array('Ken','Johnson','ken@gmail.com','21');
$custData[]=$cust1;
$custData[]=$cust2;
$custData[]=$cust3;

if($pdo_obj->insertData('customers',$fieldNames,$custData))
{
	echo "Data inserted!\n";
}


//select data
$selectQuery='select * from customers where age<?';
$results=$pdo_obj->selectData($selectQuery, array(25));
for($i=0; $i<sizeof($results);$i++)
{
	echo $results[$i]['first_name']." ".$results[$i]['first_name']." ".$results[$i]['email']." ".$results[$i]['age']."<br>"; 
}


//update data
$updateQuery='update customers set email="john.doe@gmail.com" where email=?';
echo $pdo_obj->updateData($updateQuery,array("john@gmail.com")) . " row updated!";


//delete data
$deleteQuery="delete from customers where email=?";
echo $pdo_obj->deleteData($deleteQuery,array("john.doe@gmail.com")) . " row updated!";

Search within Codexpedia

Custom Search

Search the entire web

Custom Search