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
Related Posts