>我已经在PDO上创建了一个数据库抽象层，以避免在我的脚本周围创建多个查询，这将很难维护。
我的DBAL不是很宽泛;它负责简单的任务，如插入、更新和选择（有或没有加入）。它没有涵盖更高级的内容，例如从多个表中选择等。
我的 DBAL 提出的问题是，当一个 HTTP 请求中有更多相同类型的查询时，它会混淆查询。例如，我的脚本中有三个 select 语句，第一个有效，另外两个无效。我尝试创建一个flush方法来清除查询以前填充的属性，但它不起作用，而且我的想法也没了。我还没有准备好摆脱我的类并重新开始编写查询 - 以这种方式编写它们非常容易。
无论如何，这就是我对我的类进行一些查询的方式：
$insert_update_select = array(
'COLUMNS' => array(
'column_name1' => 'data_to_update_or_insert1',
'column_name2' => 'data_to_update_or_insert2'
),
'WHERE' => array('x > y', 'y < x'),
'ORDER' => array('ASC' => 'column_name1'),
'LIMIT' => array(0, 5),
);
// This query works with updating, inserting and selecting
$db = new db();
$db->insert('table_name', $insert_update_select);
$db->update('table_name', $insert_update_select);
$db->select('table_name', $insert_update_select);
不要问我如何连接表;我实际上忘记了我自己的语法是如何工作的，哈哈。（必须努力记住）
无论如何，这是我的班级：
<?php
class db
{
private $db_type = 'mysql';
private $db_host = 'localhost';
private $db_user = 'root';
private $db_pass = '';
private $db_name = 'imgzer';
private $db;
private $db_connection = '';
private $insert_data = '';
private $update_data = '';
private $select_data = '';
private $condition_data = '';
private $order_data = '';
private $limit_data = '';
private $join_data = array();
private $query;
private $table;
private $return_data;
private $affected_rows;
private $return_id;
// Database tables
const USERS_TABLE = 'imgzer_users';
const CONFIG_TABLE = 'imgzer_config';
public function __construct()
{
$this->db_connection = "$this->db_type:host=$this->db_host;dbname=$this->db_name";
$this->db = new PDO($this->db_connection, $this->db_user, $this->db_pass);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
unset($this->db_pass);
}
public function open()
{
if ($this->db)
{
return true;
}
return false;
}
public function close()
{
if ($this->db->close())
{
return true;
}
return false;
}
private function build_array($type, $data, $join_data = array())
{
if (empty($data))
{
return;
}
$type = strtoupper($type);
$this->update_data = '';
$this->select_data = '';
$data_index = 0;
$data_length = sizeof($data);
$last_row = $data_length - 1;
switch ($type)
{
case 'INSERT':
if (!is_array($data))
{
return;
}
$this->insert_data = '(';
foreach ($data as $column => $value)
{
$this->insert_data .= $column . (($data_index != $last_row) ? ', ' : '');
$data_index++;
}
$data_index = 0;
$this->insert_data .= ') ';
$this->insert_data .= 'VALUES (';
foreach ($data as $column => $value)
{
$this->insert_data .= '?' . (($data_index != $last_row) ? ', ' : '');
$data_index++;
}
$this->insert_data .= ') ';
break;
case 'UPDATE':
$this->update_data = '';
foreach ($data as $column => $value)
{
$this->update_data .= $column . ' = ?' . (($data_index != $last_row) ? ', ' : '');
$data_index++;
}
break;
case 'SELECT':
if (empty($join_data))
{
return;
}
if (is_array($join_data))
{
$from_table = array_keys($join_data['FROM']);
$join_table = array_keys($join_data['TABLES']);
$this->select_data = implode(', ', array_flip($data)) . ' FROM ' ;
$this->select_data .= $from_table[0] . ' ' . $join_data['FROM'][$from_table[0]] . ' ';
for ($i = 0; $i < sizeof($this->join_data); $i++)
{
$this->select_data .= $this->get_join_type($join_data['JOIN']). ' ';
$this->select_data .= $join_table[$i] . ' ' . $join_data['TABLES'][$join_table[$i]];
$this->select_data .= $this->join_data[$i];
}
$this->select_data = rtrim($this->select_data, ' ');
}
else
{
if (!isset($data[0]))
{
$data = array_flip($data);
}
$this->select_data = implode(', ', $data) . ' FROM ' . $this->table . ' ';
}
break;
}
}
private function set_join($on)
{
if (empty($on))
{
return;
}
if (is_array($on))
{
for ($i = 0; $i < sizeof($on); $i++)
{
$on[$i] = ' ON (' . implode(' AND ', $on[$i]) . ') ';
}
}
$this->join_data = $on;
}
private function set_order($order)
{
if (empty($order))
{
return;
}
$this->order_data = ' ORDER BY ';
if (is_array($order))
{
$data_index = 0;
$data_size = sizeof($order) - 1;
foreach ($order as $order_type => $column)
{
if ($order_type != 'ASC' && $order_type != 'DESC')
{
throw new Exception('Order type in SQL has to be either ASC or DESC');
return;
}
$this->order_data .= $column . ' ' . $order_type . (($data_index != $data_size) ? ', ' : '');
$data_index++;
}
return;
}
$this->order_data .= $order;
}
private function set_limit($limit)
{
if (empty($limit))
{
return;
}
if (sizeof($limit) > 2)
{
return;
}
if (sizeof($limit) == 1)
{
$limit = array(0, $limit[0]);
}
if (is_array($limit))
{
$limit = implode(', ', $limit);
}
$this->limit_data = " LIMIT {$limit}";
}
private function set_where($condition)
{
if (empty($condition))
{
return;
}
if (is_array($condition))
{
$condition = implode(' AND ', $condition);
}
$this->condition_data = " WHERE $condition";
}
public function in_set($where_ary)
{
$where_str = implode(', ', $where_ary);
$where_str = substr($where_str, 0, -2);
$where_str = 'IN (' . $where_str . ')';
return $where_str;
}
/*
* Example usage:
* $insert_ary = array('col_1' => 'col_data_1', 'col_2' => 'col_data_2');
* $condition_ary = array('col_1 > 5', 'col_2 <> 10');
* $order_ary = array('ASC' => 'col_1', 'DESC' => 'col_2');
* $limit = array($start = 0, $limit = 5);
* $instance->insert('my_table', $insert_ary, $condition_ary, $order_ary, $limit);
*/
public function insert($table, $data, $return_id = false)
{
$data = $this->data_abstract($data);
// Prepare the arrays
$this->build_array('INSERT', $data['COLUMNS']);
$this->set_where($data['WHERE']);
$this->set_order($data['ORDER']);
$this->set_limit($data['LIMIT']);
$sql = 'INSERT INTO ' . $table . ' ';
$sql .= $this->insert_data;
$sql .= $this->condition_data;
$sql .= $this->order_data;
$sql .= $this->limit_data;
$this->query = $this->db->prepare($sql);
$param_index = 1;
foreach ($data['COLUMNS'] as $column => &$value)
{
$this->query->bindParam($param_index, $value);
$param_index++;
}
$this->query->execute();
if ($return_id)
{
$this->return_id = $this->query->last_insert_id();
}
else
{
$this->affected_rows = $this->query->rowCount();
}
}
public function update($table, $data, $return_id = false)
{
$data = $this->data_abstract($data);
// Prepare the arrays
$this->build_array('UPDATE', $data['COLUMNS']);
$this->set_where($data['WHERE']);
$this->set_order($data['ORDER']);
$this->set_limit($data['LIMIT']);
$sql = 'UPDATE ' . $table . ' SET ';
$sql .= $this->update_data;
$sql .= $this->condition_data;
$sql .= $this->order_data;
$sql .= $this->limit_data;
$this->query = $this->db->prepare($sql);
$param_index = 1;
foreach ($data['COLUMNS'] as $column => &$value)
{
$this->query->bindParam($param_index, $value);
$param_index++;
}
$this->query->execute();
if ($return_data)
{
$this->return_id = $this->query->last_insert_id();
}
else
{
$this->affected_rows = $this->query->rowCount();
}
}
/*
* Joining example:
* $join_data = array(
* 'TABLES' => array('table_2' => 't2', 'table_3' => 't3'),
* 'JOIN' => 'LEFT',
* 'ON' => array(
* array('colx > 15', 'coly < 20'),
* array('fieldx > 15', 'fieldy < 20')
* ),
*);
*/
public function select($table, $data, $join = false, $fetch_type = 'assoc')
{
$data = $this->data_abstract($data);
if ($join)
{
if (!is_array($table))
{
throw new Exception('Table has to be associated with a short index');
return;
}
$this->set_join($join['ON']);
$table = array_merge(array('FROM' => $table), $join);
}
// Globalize table name if not joins are used
$this->table = $table;
// Prepare the arrays
$this->build_array('SELECT', $data['COLUMNS'], $table);
$this->set_where($data['WHERE']);
$this->set_order($data['ORDER']);
$this->set_limit($data['LIMIT']);
$sql = 'SELECT ';
$sql .= $this->select_data;
$sql .= $this->condition_data;
$sql .= $this->order_data;
$sql .= $this->limit_data;
$this->query = $this->db->prepare($sql);
$result = $this->query->execute();
$fetch_type = ($fetch_type == 'assoc') ? PDO::FETCH_ASSOC : PDO::FETCH_NUM;
$fetched_data = $this->query->fetchAll($fetch_type);
$data_result = $fetched_data;
if (sizeof($fetched_data) == 1)
{
$data_result = $fetched_data[0];
}
$this->return_data = $data_result;
// Clear the result
//$this->query->closeCursor();
}
public function fetch()
{
return $this->return_data;
}
public function affected_rows()
{
return $this->affected_rows;
}
private function data_abstract($data)
{
$abstract_ary = array('COLUMNS' => '', 'WHERE' => '', 'ORDER' => '', 'LIMIT' => 0);
return array_merge($abstract_ary, $data);
}
private function get_join_type($type)
{
switch ($type)
{
default:
case 'LEFT':
return 'LEFT JOIN';
break;
case 'RIGHT':
return 'RIGHT JOIN';
break;
case 'INNER':
return 'INNER JOIN';
break;
case 'NORMAL':
case 'JOIN':
return 'JOIN';
break;
}
}
private function flush()
{
unset($this->query, $this->insert_data, $this->update_data, $this->select_data);
}
}
$db = new db();
?>
它有什么问题（可能很多），我如何真正让它有效地工作？
不要让它有状态。
即使不看代码，我也会告诉你问题所在：摆脱$this->stmt变量。
出于某种原因，所有 DBAL 编写器都强烈倾向于这样的变量......将状态引入他们的类，从而使它无法使用。
方法调用都必须是原子的，每个方法调用执行所有必要的操作并返回所有请求的数据。同时在类变量中不保存任何内容。就这么简单。在这种极少数情况下，当必须进一步使用 PDOStatement 对象时 - 返回这个对象，不要将其保存在里面。否则，只需返回请求的数据。
我还建议摆脱整个DBAL，因为它是出于好意而编写的，但我可以肯定地说，实现结果不太有用，但它实际上使您的代码在许多方面变得更糟 - 可读性，灵活性，可维护性。为了追求虚构的可用性，你只从SQL中节省了一两个字，但使整个应用程序代码变得不可靠。
不过，你不会听我的。需要一些维护应用程序的经验才能理解我的观点。