Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save PrafullaKumarSahu/70daaa985f885c3421f8 to your computer and use it in GitHub Desktop.
Save PrafullaKumarSahu/70daaa985f885c3421f8 to your computer and use it in GitHub Desktop.
Learning PDO in as possible correct way .
<?php
class Database
{
private $conn;
private $handle;
public function __construct()
{
$dbhost = "localhost";
$dbname = "pdo_test";
$dbuser = "root";
$dbpass = "";
$charset = "utf8";
/**
* -database driver, host, db ( schema ) name and charset, as well as less frequently used port and unix_socket are going to DSN
* -username and passwword are going to constructor
* -al other options go to options array
*/
$dsn = "mysql:host=$dbhost;dbname=$dbname;charset=$charset"; // DSN is a semicolon-delimited string consist of param = vale pairs.( DSN = Data Source Name )
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
/**
* Important Notes-
* -Unlike old mysql_ * functions, which can be used anywhere in the code,
PDO is stored in a regular variable,
which means it can be inaccessible inside function - so,
one have to make it accessible ,
by means of the global directive or it's more complicated analogue - an IoC container
* -Connection have to be done only oncee !
No connects in every function !
No connects in every class constructor !
Connection have to be done once and this variable have to be used all the way,
Otherwise multiple connection will be created ,
which will eventually kill your database server
*/
try
{
$this->conn = new PDO( $dsn, $dbuser, $dbpass, $opt );
//echo "Database connection successful.<br />";
}
catch(PDOException $e)
{
echo "Error in database connection !!!<br />";
echo $e->getMessage();
}
}
public function test($test)
{
echo "<br />".$test;
}
public function create($table, $data)
{
$query = "CREATE TABLE $table ($data);";
$this->handle = $this->conn->prepare($query);
if($this->handle->execute())
{
echo "$table created.<br />";
}
}
public function insert($table, $data)
{
$keys = implode(',', array_keys($data));
foreach($data as $key => $val)
$data[$key] = "'".$val."'";
$vals = implode(",",$data);
$query = "INSERT INTO $table ($keys) VALUES ($vals);";
// echo $query;
$this->handle = $this->conn->prepare($query);
if($this->handle->execute())
{
return $this->conn->lastInsertId();
}
else
{
echo "Can not be added to database !!!";
}
}
public function select($table,$condition)
{
$where = array();
foreach($condition as $key => $value)
{
$where[] = "$key='$value'";
}
$var = implode(' AND ', $where);
$query = "SELECT * FROM $table WHERE $var;";
$this->handle = $this->conn->prepare($query);
$indexes = $this->handle->execute();
while($result = $this->handle->fetchAll(PDO::FETCH_ASSOC))
{
return($result);
}
}
function update($table, $data, $condition)
{
$array=array();
foreach($data as $key=>$val){
$array[]=$key."='".$val."'";
}
$values=implode(',',$array);
$where = array();
foreach($condition as $key => $value)
{
$where[] = "$key='$value'";
}
$var = implode(' AND ', $where);
$query="UPDATE $table SET $values WHERE $var LIMIT 1;";
// echo $query;
$this->handle=$this->conn->prepare($query);
if($this->handle->execute())
{
return true;
}
else
{
return FALSE;
}
}
public function delete($table,$condition)
{
$where = array();
foreach($condition as $key => $value)
{
$where[] = "$key='$value'";
}
$var = implode(' AND ', $where);
$query = "DELETE FROM $table WHERE $var LIMIT 1;";
// echo $query;
$this->handle = $this->conn->prepare($query);
if($this->handle->execute())
{
return true;
}
else
{
echo "Delete operation is not performed due to some problem !!<br />";
}
}
public function login( $table, $email, $password )
{
$query = "SELECT id, first_name, last_name, email, password FROM $table WHERE email = :email AND password = :password";
$this->handle = $this->conn->prepare( $query, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ) );
$this->handle->execute(array(':email' => $email, ':password' => $password ) );
$count = $this->handle->rowCount();
if( $count == 1 ) {
$result = $this->handle->fetch( PDO::FETCH_ASSOC );
/**
* Try using PDO::FETCH_LAZY allows all three (numeric associative and object) methods
without memory overhead
* PDO::FETCH_OBJ returns object
* PDOStatement::fetchAll() use this when data need to be passed to template and not to be outputed directly
*/
return $result;
}
}
public function __destruct()
{
$this->conn = null;
if(!$this->conn)
{
// echo "<br />database disconnected !!";
}
}
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment