This is a database class I designed, which uses methods chaining to achieve its purpose of easily manipulating mysql database commands in one single line. The core script is completed, but I do need to find ways to optimize the script. I've also included a few examples on how the database class can be used. Please look at it and give me feedbacks on how I can hope to improve it. Thanks.
<?php
class Database extends mysqli{
protected $mysqli, $result, $table, $column, $where, $value, $limit, $order, $query;
// The constructor.
public function __construct($host, $user, $pass, $db){
$this->mysqli = new mysqli($host, $user, $pass, $db)
or die("Error connecting to the database {$db}");
}
// The destructor.
public function __destruct(){
$this->mysqli->close();
}
// Prepares a query for future execution.
protected function prepareQuery(){
if ($query = $this->mysqli->prepare($this->query)) {
trigger_error("Problem preparing query ($this->query) ".$this->mysqli->error, E_USER_ERROR);
}
return $this;
}
// Reset mysql query, must be used at the end of method chaining.
protected function reset(){
unset($this->table);
unset($this->column);
unset($this->where);
unset($this->value);
unset($this->limit);
unset($this->order);
unset($this->result);
unset($this->query);
}
// Grabs the table data.
public function table($table){
$this->table = $table;
return $this;
}
// This method is used together with create() to generate new mysql tables.
public function addcolumn($column, $key = "false"){
$i = 0;
foreach($column as $col => $type){
$this->column .= "{$col} {$type}";
$this->column .= ($i == 0 and $key == "true")?" NOT NULL AUTO_INCREMENT PRIMARY KEY ":"";
$this->column .= ($i < count($column) - 1)?", ":"";
$i++;
}
return $this;
}
// the method managecolumn() is chained with method alter() to edit settings for columns in a given table.
public function managecolumn($column, $type="", $action = ""){
if($action == "add") $this->column = "ADD column {$column} {$type}";
if($action == "unique") $this->column = "ADD UNIQUE ({$column})";
if($action == "drop") $this->column = "DROP column {$column}";
if($action == "modify") $this->column = "MODIFY {$column} {$type}";
if($action == "action" and is_array($column)) $this->column = "CHANGE {$column[0]} {$column[1]} {$type}";
return $this;
}
// the method fromcolumn() needs to be used for the method select().
public function fromcolumn($column){
$this->column = (is_array($column))?implode(",", $column):$column;
return $this;
}
// the method setcolumn() needs to be chained with method update().
public function setcolumn($column){
$i = 0;
foreach($column as $col => $val){
$this->column .= "{$col} = '{$val}'";
$this->column .= ($i < count($column) - 1)?", ":"";
$i++;
}
return $this;
}
// Another method for column manipulation, this one should be called with insert() method.
public function intocolumn($column){
$this->column = (is_array($column))?implode(", ", $column):$column;
$this->column = "({$this->column})";
return $this;
}
// It stores values for insert() method.
public function value($value){
$i = 0;
foreach($value as $val){
$valuestring .= "'{$val}'";
$valuestring .= ($i < (count($value)-1))?", ":" ";
$i++;
}
$this->value = $valuestring;
return $this;
}
// The where() method is mostly used as preliminary step to select or update mysql database data.
public function where($where, $comparison = "", $logic = ""){
$i = 0;
$comparison = (empty($comparison))?"=":$comparison;
$logic = (empty($logic))?array_fill(0,count($where)," AND"):$logic;
foreach ($where as $col => $val){
$wherestring .= (is_array($comparison))?" {$col} {$comparison[$i]} '{$val}'" : " {$col} {$comparison} '{$val}'";
$wherestring .= ($i < (count($where)-1))?" {$logic[$i]}" :" ";
$i++;
}
$this->where = $wherestring;
return $this;
}
// sets the limit of data retrieved.
public function limit($limit){
$this->limit = $limit;
return $this;
}
// determines the order of data displayed.
public function order($order){
$this->order = $order;
return $this;
}
// fetch data of any type, must be chained with method select().
public function fetch($action = ""){
if($action == "row") $result = $this->result->fetch_row();
if($action == "assoc") $result = $this->result->fetch_assoc();
if($action == "array") $result = $this->result->fetch_array();
if($action == "field") $result = $this->result->fetch_field();
if($action == "fields") $result = $this->result->fetch_fields();
if($action == "object") $result = $this->result->fetch_object();
if($action == "all") $result = $this->result->fetch_all();
if($action == "num") $result = $this->result->num_rows();
$this->reset();
return $result;
}
// the create new table method.
public function create(){
$query = "CREATE TABLE {$this->table} ({$this->column})";
$this->mysqli->query($query) or die("Mysql Error, cannot create table {$this->table}");
$this->reset();
}
// the alter existing table method.
public function alter(){
$query = "ALTER TABLE {$this->table} {$this->column}";
$this->mysqli->query($query) or die("Mysql Error, cannot alter table {$this->table}");
$this->reset();
}
// the drop table method.
public function drop(){
$query = "DROP TABLE {$this->table}";
$this->mysqli->query($query) or die("Mysql Error, cannot drop table {$this->table}");
$this->reset();
}
// the select data method.
public function select(){
if(empty($this->column)) $this->column = "*";
$query = "SELECT {$this->column} FROM {$this->table}";
$query .= (!empty($this->where))?" WHERE {$this->where}":"";
$query .= (!empty($this->order))?" ORDER BY {$this->order}":"";
$query .= (!empty($this->limit))?" LIMIT {$this->limit}":"";
echo $query."<br>";
$this->result = $this->mysqli->query($query);
return $this;
}
// the update data method.
public function update(){
$query = "UPDATE {$this->table} SET {$this->column} WHERE {$this->where}";
$this->mysqli->query($query) or die("Mysql Error, cannot update table {$this->table}");
$this->reset();
}
// the insert data method.
public function insert(){
$query = "INSERT INTO {$this->table} {$this->column} VALUES ({$this->value})";
$this->mysqli->query($query) or die("Mysql Error, cannot insert into table {$this->table}");
$this->reset();
}
// the delete data method.
public function delete(){
$query = "DELETE FROM {$this->table} WHERE {$this->where}";
$this->mysqli->query($query) or die("Mysql Error, cannot delete from table {$this->table}");
$this->reset();
}
}
?>
To connect to mysqli:
$db = new Database($hostname, $username, $password, $database);
To select data from mysql table and return an array:
$row = $db->table($tablename)->fromcolumn($columnname)->where(array(column1=>data1,column2=>data2))->order($order)->limit($limit)->select()->fetch("array");
To update data in mysql table:
$db->table($tablename)->setcolumn(array($column1=>data1, $column2=>data2))->where(array($column3=>data3))->update();
To insert data into mysql table:
$db->table($tablename)->intocolumn(array($column1, $column2))->value(array($var1, $var2))->insert();
