-
-
Save eimg/5055173 to your computer and use it in GitHub Desktop.
<?php | |
# PDO Wrapper, supporting MySQL and Sqlite | |
# Usage: | |
# $db = new db(); | |
# | |
# // table, data | |
# $db->create('users', array( | |
# 'fname' => 'john', | |
# 'lname' => 'doe' | |
# )); | |
# | |
# // table, where, where-bind | |
# $db->read('users', "fname LIKE :search", array( | |
# ':search' => 'j%' | |
# )); | |
# | |
# // table, data, where, where-bind | |
# $db->update('users', array( | |
# 'fname' => 'jame' | |
# ), 'gender = :gender', array( | |
# ':gender' => 'female' | |
# )); | |
# | |
# // table, where, where-bind | |
# $db->delete('users', 'lname = :lname', array( | |
# ':lname' => 'doe' | |
# )); | |
class db | |
{ | |
private $config = array( | |
# "dbdriver" => "sqlite", | |
# "sqlitedb" => "path/to/db.sqlite" | |
"dbdriver" => "mysql", | |
"dbuser" => "root", | |
"dbpass" => "", | |
"dbname" => "test" | |
); | |
function db() { | |
$dbhost = $this->config['dbhost']; | |
$dbuser = $this->config['dbuser']; | |
$dbpass = $this->config['dbpass']; | |
$dbname = $this->config['dbname']; | |
# $sqlitedb = $this->config['sqlitedb']; | |
$options = array( | |
PDO::ATTR_PERSISTENT => true, | |
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION | |
); | |
try { | |
switch($this->config["dbdriver"]) { | |
case "sqlite": | |
$conn = "sqlite:{$sqlitedb}"; | |
break; | |
case "mysql": | |
$conn = "mysql:host={$dbhost};dbname={$dbname}"; | |
break; | |
default: | |
echo "Unsuportted DB Driver! Check the configuration."; | |
exit(1); | |
} | |
$this->db = new PDO($conn, $dbuser, $dbpass, $options); | |
} catch(PDOException $e) { | |
echo $e->getMessage(); exit(1); | |
} | |
} | |
function run($sql, $bind=array()) { | |
$sql = trim($sql); | |
try { | |
$result = $this->db->prepare($sql); | |
$result->execute($bind); | |
return $result; | |
} catch (PDOException $e) { | |
echo $e->getMessage(); exit(1); | |
} | |
} | |
function create($table, $data) { | |
$fields = $this->filter($table, $data); | |
$sql = "INSERT INTO " . $table . " (" . implode($fields, ", ") . ") VALUES (:" . implode($fields, ", :") . ");"; | |
$bind = array(); | |
foreach($fields as $field) | |
$bind[":$field"] = $data[$field]; | |
$result = $this->run($sql, $bind); | |
return $this->db->lastInsertId(); | |
} | |
function read($table, $where="", $bind=array(), $fields="*") { | |
$sql = "SELECT " . $fields . " FROM " . $table; | |
if(!empty($where)) | |
$sql .= " WHERE " . $where; | |
$sql .= ";"; | |
$result = $this->run($sql, $bind); | |
$result->setFetchMode(PDO::FETCH_ASSOC); | |
$rows = array(); | |
while($row = $result->fetch()) { | |
$rows[] = $row; | |
} | |
return $rows; | |
} | |
function update($table, $data, $where, $bind=array()) { | |
$fields = $this->filter($table, $data); | |
$fieldSize = sizeof($fields); | |
$sql = "UPDATE " . $table . " SET "; | |
for($f = 0; $f < $fieldSize; ++$f) { | |
if($f > 0) | |
$sql .= ", "; | |
$sql .= $fields[$f] . " = :update_" . $fields[$f]; | |
} | |
$sql .= " WHERE " . $where . ";"; | |
foreach($fields as $field) | |
$bind[":update_$field"] = $data[$field]; | |
$result = $this->run($sql, $bind); | |
return $result->rowCount(); | |
} | |
function delete($table, $where, $bind="") { | |
$sql = "DELETE FROM " . $table . " WHERE " . $where . ";"; | |
$result = $this->run($sql, $bind); | |
return $result->rowCount(); | |
} | |
private function filter($table, $data) { | |
$driver = $this->config['dbdriver']; | |
if($driver == 'sqlite') { | |
$sql = "PRAGMA table_info('" . $table . "');"; | |
$key = "name"; | |
} elseif($driver == 'mysql') { | |
$sql = "DESCRIBE " . $table . ";"; | |
$key = "Field"; | |
} else { | |
$sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';"; | |
$key = "column_name"; | |
} | |
if(false !== ($list = $this->run($sql))) { | |
$fields = array(); | |
foreach($list as $record) | |
$fields[] = $record[$key]; | |
return array_values(array_intersect($fields, array_keys($data))); | |
} | |
return array(); | |
} | |
} |
Hi,
Let me first say that you did an excellent job, clean code and very powerfull !
I think this is the most stupid quest you have ever heard but, I pars an query to the read function
class ClassRead extends db {
function ShowTeam(){
$q = new db();
$q -> read("Team",
"Name=:search",
array(':search' => 'Team 1')
);
}
}
after that I want to use this function ClassRead::ShowTeam();
. If I do this I got nothing.
If I put print_r($rows);
in the read function, above the return $rows;
, i see the result of my query in array format. That is also my problem. I don't want the array format, just a clean string of my result. And im not skilled enough, i guess, to manage this.
If i use a foreach loop i get the result i want (putting it in the read function). But i want the function read so dynamic possible so that i can pars almost every query in to it.
Hope you can help me
When I run the following code:
$db->update('dispatch', array( 'name' => $name, 'phone' => $phone), 'id = :id', array( ':id' => $id));
Nothing updates, if I refresh the page nothing is affected. Is there something I am missing?
private $config = array(
# "dbdriver" => "sqlite",
# "sqlitedb" => "path/to/db.sqlite"
"dbdriver" => "mysql",
"dbuser" => "root",
"dbpass" => "",
"dbname" => "test"
);
Missed setting dbhost variable in config array...
Add that variable and check. Hope this help.
Thanks it´s very useful.
Clean code =)