Skip to content

Instantly share code, notes, and snippets.

@ndunks ndunks/DB.php
Created Dec 8, 2017

Embed
What would you like to do?
PHP DB Class for MySQL
<?php
class DB
{
var $con = null;
var $result = null;
var $has_result = false;
var $error = false;
var $last_query;
var $affected_rows;
function __construct($config)
{
$this->con = new mysqli($config['host'], $config['user'], $config['pass'], $config['name']);
if ($this->con->connect_error)
{
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
}
function select($table, $where_col = null, $where_val = null, $order = null, $limit = null){
$query = "SELECT * FROM `$table`";
if($where_col){
$where_val = $this->escape($where_val);
if(!is_numeric($where_val))
{
$where_val = "'$where_val'";
}
$query .= " WHERE `$where_col` = $where_val";
}
if($order)
$query .= " ORDER BY $order";
if($limit)
$query .= " LIMIT $limit";
return $this->query($query);
}
function get_insert_id(){
return $this->con->insert_id;
}
function &query($qry)
{
$this->clear();
$this->result = $this->con->query($qry);
$this->affected_rows = $this->con->affected_rows;
if($this->con->error)
{
$this->error = $this->con->error;
}
$this->last_query = $qry;
$this->has_result = is_object($this->result) && $this->result->num_rows > 0 && $this->affected_rows;
return $this;
}
function query_scalar($qry)
{
$this->query($qry);
if( !$this->has_result) return 0;
$qry = $this->result->fetch_row();
return $qry[0] ? $qry[0] : 0;
}
function result_list($col_index = 0){
$data = array();
if($this->has_result)
{
while($row = $this->result->fetch_row())
{
$data[] = $row[$col_index];
}
}
return $data;
}
function &result_array()
{
$data = array();
if($this->has_result)
{
while($row = $this->result->fetch_assoc())
{
$data[] = $row;
}
}
return $data;
}
function result_pair($name = 'name', $value = 'value'){
$data = array();
if($this->has_result)
{
while($row = $this->result->fetch_assoc())
{
$key = $row[$name];
$alias = 0;
while ( isset($data[$key]) ){
$alias++;
$key = "{$row[$name]}_$alias";
}
$data[ $key ] = $row[$value];
}
}
return $data;
}
function row_array()
{
if($this->has_result)
{
return $this->result->fetch_assoc();
}else return array();
}
function delete($tbl, $where_col, $where_val = null)
{
$query = "delete from `$tbl` where ";
if(empty($where_val) && is_array($where_col))
{
$query .= $this->parse_col_val($where_col);
}else
{
$query .= "`" . $this->escape($where_col) . "` = '" . $this->escape($where_val) . "'";
}
return $this->query($query);
}
function insert($tbl, &$data, $escape_val = true)
{
list($columns, $values) = $this->parseColValues($data, $escape_val);
if( $this->query("INSERT INTO `$tbl`($columns) VALUES ($values)")->affected_rows )
{
return $this->con->insert_id;
}else return false;
}
function insertUpdate($tbl, &$data, $escape_val = true)
{
list($columns, $values) = $this->parseColValues($data, $escape_val);
$cols = array_keys($data);
//We think first col is a unique id, so we remove it
array_shift($cols);
foreach ($cols as &$col){
$col = "`$col` = VALUES(`$col`)";
}
$query = "INSERT INTO `$tbl`($columns) VALUES ($values) " .
'ON DUPLICATE KEY UPDATE ' . implode(', ', $cols);
return $this->query($query)->affected_rows;
}
function replace($tbl, &$data, $escape_val = true)
{
list($columns, $values) = $this->parseColValues($data, $escape_val);
if( $this->query("REPLACE INTO `$tbl`($columns) VALUES ($values)")->affected_rows )
{
// if 1 == new insert, 2 == old deleted, new inserted
return $this->con->insert_id;
}else return false;
}
function parseColValues(&$data, $escape_val = true){
$values = $this->parseValues($data, $escape_val);
return [ implode(', ', array_keys($values)), implode( ', ', $values ) ];
}
function parseValues(&$data, $escape_val = true){
$values = array();
if($escape_val)
{
foreach ($data as $key => $val)
{
$values["`$key`"] = is_numeric($val)? $val : "'" . $this->escape($val) . "'";
}
}else
{
foreach ($data as $key => $val)
{
$values["`$key`"] = is_numeric($val)? $val : "'" . $val . "'";
}
}
return $values;
}
function update($tbl, $col, $val, $where_col = null, $where_val = null)
{
$query = "update $tbl set ";
if(is_array($col))
{
$query .= $this->parse_col_val($col) . ' where ';
if(is_array($val)){
$query .= $this->parse_col_val($val);
}elseif(!empty($val) && !empty($where_col) ){
$query .= "`$val` = '" . $this->escape($where_col) . "'";
}else{
throw new Exception("Invalid argument", 1);
}
}elseif(is_string($col) && is_string($where_col) && !empty($where_col))
{
$query .= "`$col` = '" . $this->escape($val) .
"' where `$where_col` = '" . $this->escape($where_val) . "'";
}else
{
throw new Exception("Ambigous update query, Iam confused :-(", 1);
return false;
}
$this->query($query);
return $this->error ? false : $this->affected_rows;
}
private function parse_col_val($array)
{
$cols = array();
foreach ($array as $c => &$v)
{
$cols[] = "`$c` = '" . $this->escape($v) . "'";
}
return implode(', ', $cols);
}
function clear()
{
if(is_object($this->result))
$this->result->free_result();
$this->affected_rows= 0;
$this->result = null;
$this->has_result = false;
$this->error = false;
}
function escape($str)
{
return $this->con->escape_string($str);
}
function real_escape($str)
{
return $this->con->real_escape_string($str);
}
function close()
{
$this->clear();
$this->con->close();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.