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