常用的php数据库,PHP常用的数据库操作

PHP中，对MySQL数据库常用的操作。

class MyDbi

{

private $mysqli;

private $result;

//构造函数

public function __construct($db_host, $db_user, $db_pwd, $db_name)

{

//在面向对象的方式中，mysqli被封装成一个类

$this->mysqli = new mysqli($db_host, $db_user, $db_pwd, $db_name);

$this->result = null;

if (mysqli_connect_errno())

{

$this->mysqli = false;

exit("连接失败，原因为：" . mysqli_connect_error());

}

$this->mysqli->query("SET NAMES 'utf8';");

$this->mysqli->query("set time_zone = '+8:00';");

}

/**

* SQL执行，$sql是执行语句

*/

private function db_query($sql)

{

if (!$this->result = $this->mysqli->query($sql))

{

exit("SQL执行失败！$sql
" . mysqli_error()); //.mysql_error()

}

return $this->result;

}

/**

* total:计算表中的个数

* 参数：$tabName是表名；$idName是字段名，默认是id；$where是选择条件

*/

public function total($tabName, $idName = 'id', $where = '')

{

if (!empty($where))

$where = ' WHERE ' . $where;

$q = sprintf("SELECT %s FROM `%s`%s", $idName, $tabName, $where);

$this->db_query($q);

return $this->result->num_rows;

}

/**

* findOne：查找内容

* 参数：$tabName表名；$idName是字段名；$id是查询条件

*/

public function find($tabName, $idName, $id)

{

$id = isset($id) ? (int)$id : 0;

if ($id > 0)

{

//拼接

$q = sprintf("SELECT * FROM `%s` WHERE `%s`=%d", $tabName, $idName, $id);

$this->db_query($q);

if ($this->result && $this->result->num_rows > 0)

{

// 从结果集中取得一行作为关联数组

return $this->result->fetch_assoc();

} else

{

return null;

}

}

}

/**

* findAll：查找多个字段

* 参数：$tabName表名；$field查询的字段集合；$order是返回的顺序；$limit返回数量限制；$where是查询条件

*

*/

public function findAll($tabName, $field = array(), $order = 'id DESC', $limit =

'', $where = '')

{

if (!empty($field) && is_array($field))

{

// join() 函数把数组元素组合为一个字符串。

$field = join(",", $field);

} else

{

$field = '*';

}

if (!empty($where))

$where = ' WHERE ' . $where;

if ($limit == '')

{

//选择全部

$q = sprintf("SELECT %s FROM `%s`%s ORDER BY %s", $field, $tabName, $where, $order);

} else

{

//有数量的限制

$q = sprintf("SELECT %s FROM `%s`%s ORDER BY %s LIMIT %s", $field, $tabName, $where,

$order, $limit);

}

$this->db_query($q);

if ($this->result && $this->result->num_rows > 0)

{

$data = array();

while ($row = $this->result->fetch_assoc())

{

$data[] = $row;

}

return $data;

} else

{

return null;

}

}

/**

* del:在表中删除记录

* 参数：$tabName表名；$idName是字段名；$id是查询条件

* 返回：boolen值

*/

public function del($tabName, $idName, $id)

{

$id = isset($id) ? (int)$id : 0;

if ($id > 0)

{

$q = sprintf("DELETE FROM `%s` WHERE `%s`=%d", $tabName, $idName, $id);

return $this->mysqli->query($q) ? true : false;

}

}

/**

* add:增加一条记录

* 参数：$tabName表名；

*/

public function add($tabName, $postList = array(), $autoList = array())

{

if (isset($postList['submitted']))

unset($postList['submitted']);

$postArray = $this->getList($postList);

$autoArray = $this->getList($autoList);

$fieldList = $postArray[0] . ',' . $autoArray[0];

$valueList = $postArray[1] . ',' . $autoArray[1];

$fieldList = $this->delDot($fieldList);

$valueList = $this->delDot($valueList);

$q = sprintf("INSERT INTO `%s` (%s) VALUES (%s)", $tabName, $fieldList, $valueList);

return $this->mysqli->query($q) ? true : false;

}

//update

public function update($tabName, $postList = array(), $autoList = array(), $idName,

$id = 0)

{

if (isset($postList['submitted']))

unset($postList['submitted']);

if (isset($postList['id']))

{

$id = $postList['id'];

unset($postList['id']);

} else

{

$id = isset($id) ? (int)$id : 0;

}

if ($id > 0)

{

$valueList = $this->getValue($postList) . ',' . $this->getValue($autoList);

$valueList = $this->delDot($valueList);

}

$q = sprintf("UPDATE `%s` SET %s WHERE `%s`=%d", $tabName, $valueList, $idName,

$id);

return $this->mysqli->query($q) ? true : false;

}

//getList

private function getList($list = array())

{

$fieldList = '';

$valueList = '';

if (!empty($list))

{

foreach ($list as $k => $v)

{

$fieldList .= $k . ',';

$valueList .= sprintf("'%s',", $this->escape_data($v));

}

$fieldList = $this->delDot($fieldList);

$valueList = $this->delDot($valueList);

}

return array($fieldList, $valueList);

}

//getValue

private function getValue($list = array())

{

$data = '';

if (!empty($list))

{

foreach ($list as $k => $v)

{

$data .= sprintf("`%s`='%s',", $k, $this->escape_data($v));

}

$data = $this->delDot($data);

}

return $data;

}

//del_dot

private function delDot($str)

{

$str = rtrim($str, ',');

$str = ltrim($str, ',');

return $str;

}

//Escape

private function escape_data($data)

{

if (ini_get('magic_quotes_gpc'))

{

$data = stripslashes($data);

}

return $this->mysqli->real_escape_string(trim($data));

}

public function __destruct()

{

if (!empty($this->result))

$this->result->free();

if ($this->mysqli)

$this->mysqli->close();

unset($this->result);

$this->mysqli = false;

}

}

?>