<?php
$dbConfig = require_once(dirname(__FILE__).'/config.php');
class Db{
public $conn;
private $host = null;
private $user = null;
private $password = null;
private $database = null;
private $tablename = null;
private $dbConfig = null;
private $sql = [
'where' => null,
'orderBy' => null,
'limit' => null,
];
public function __construct($tablename = '') {
global $dbConfig;
$this->dbConfig = $dbConfig;
$this->tablename = $dbConfig['DB_PREFIX'].$tablename;
$this->user = $dbConfig['DB_USER'];
$this->host = $dbConfig['DB_HOST'];
$this->password = $dbConfig['DB_PWD'];
$this->database = $dbConfig['DB_NAME'];
$dsn = 'mysql:dbname='.$this->database.';host='.$this->host.';port=3306';
try {
$this->conn = new PDO($dsn, $this->user, $this->password); // also allows an extra parameter of configuration
} catch(PDOException $e) {
die('Could not connect to the database:<br/>' . $e);
}
}
public function table($tablename) {
$this->tablename = $this->dbConfig['DB_PREFIX'].$tablename;
$this->sql = [
'where' => null,
'orderBy' => null,
'limit' => null,
];
return $this;
}
public function getAll($fields = '*') {
$querySql = sprintf("SELECT %s FROM %s", $fields, $this->tablename);
if(!empty($this->sql['where'])) {
$querySql .= ' WHERE ' . $this->sql['where'];
}
if(!empty($this->sql['orderBy'])) {
$querySql .= ' ORDER BY ' . $this->sql['orderBy'];
}
if(!empty($this->sql['limit'])) {
$querySql .= ' LIMIT ' . $this->sql['limit'];
}
return $this->query($querySql);
}
public function getOne($fields = '*') {
$result = $this->getAll($fields);
return isset($result[0]) ? $result[0] : null;
}
public function insert($data) {
foreach ($data as $key => &$value) {
$value = addslashes($value);
}
$keys = "`".implode('`,`', array_keys($data))."`";
$values = "'".implode("','", array_values($data))."'";
$querySql = sprintf("INSERT INTO %s ( %s ) VALUES ( %s )", $this->tablename, $keys, $values);
return $this->query($querySql);
}
public function delete() {
$querySql = sprintf("DELETE FROM %s WHERE ( %s )", $this->tablename, $this->sql['where']);
return $this->query($querySql);
}
public function update($data) {
$updateFields = [];
foreach ($data as $key => $value) {
$up_value = addslashes($value);
$updateFields[] = "`$key`='$up_value'";
}
$updateFields = implode(',', $updateFields);
$querySql = sprintf("UPDATE %s SET %s", $this->tablename, $updateFields);
if(!empty($this->sql['where'])) {
$querySql .= ' WHERE ' . $this->sql['where'];
}
return $this->query($querySql);
}
public function query($querySql) {
$querystr = strtolower(trim(substr($querySql,0,6)));
$stmt = $this->conn->prepare($querySql);
$ret = $stmt->execute();
if(!$ret) print_r($stmt->errorInfo());
if($querystr == 'select') {
$retData = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $retData;
}elseif($ret && $querystr == 'insert') {
return $this->conn->lastInsertId();
}else{
return $ret;
}
}
public function limit($limit, $limitCount = null) {
if(!$limitCount) {
$this->sql['limit'] = $limit;
}else{
$this->sql['limit'] = $limit .','. $limitCount;
}
return $this;
}
public function orderBy($orderBy) {
$this->sql['orderBy'] = $orderBy;
return $this;
}
public function close() {
return $this->conn = null;
}
public function where($where) {
if(!is_array($where)) {
return null;
}
$crondsArr = [];
foreach ($where as $key => $value) {
$fieldValue = $value;
if(is_array($fieldValue)) {
$crondsArr[] = "`$key` ".$fieldValue[0]. " '" . addslashes($fieldValue[1]). "'";
}else{
$fieldValue = addslashes($fieldValue);
$crondsArr[] = "`$key`='$fieldValue'";
}
}
$this->sql['where'] = implode(' AND ', $crondsArr);
return $this;
}
}