mysql 数据库连接类_Mysqli 数据库连接类

namespace Lib;//数据库连接类

class DB {//私有的属性

private static $dbcon=false;

private $host;

private $port;

private $user;

private $pass;

private $db;

private $charset;

private $link;//私有的构造方法

privatefunction__construct($config) {

$this->host = $config['host'] ? $config['host'] : 'localhost';

$this->port = $config['port'] ? $config['port'] : '3306';

$this->user = $config['user'] ? $config['user'] : 'root';

$this->pass = $config['pass'] ? $config['pass'] : '';

$this->db = $config['db'] ? $config['db'] : '';

$this->charset = 'utf8';//连接数据库

$this->db_connect();//选择数据库

$this->db_usedb();//设置字符集

$this->db_charset();

}//连接数据库

privatefunctiondb_connect() {

$this->link = mysqli_connect($this->host . ':' . $this->port, $this->user, $this->pass);if (!$this->link) {

echo "数据库连接失败
";

echo "错误编码" . mysqli_errno($this->link) . "
";

echo "错误信息" . mysqli_error($this->link) . "
";exit;

}

}//设置字符集

privatefunctiondb_charset() {

mysqli_query($this->link, "set names {$this->charset}");

}//选择数据库

privatefunctiondb_usedb() {

mysqli_query($this->link, "use {$this->db}");

}//私有的克隆

privatefunction__clone() {

die('clone is not allowed');

}//公用的静态方法public static function getIntance($config = null) {if (self::$dbcon ==false) {

self::$dbcon=new self($config);

}returnself::$dbcon;

}//执行sql语句的方法public functionquery($sql) {

$res= mysqli_query($this->link, $sql);if(!$res) {

echo "sql语句执行失败
";

echo "错误编码是" . mysqli_errno($this->link) . "
";

echo "错误信息是" . mysqli_error($this->link) . "
";

}return$res;

}//获得最后一条记录idpublic functiongetInsertid() {return mysqli_insert_id($this->link);

}/**

* 查询某个字段

* @param

* @return string or int*/

public functiongetOne($sql) {

$query= $this->query($sql);returnmysqli_free_result($query);

}//获取一行记录,returnarray 一维数组public function getRow($sql, $type ="assoc") {

$query= $this->query($sql);if (!in_array($type, array("assoc", 'array', "row"))) {

die("mysqli_query error");

}

$funcname="mysqli_fetch_" . $type;return$funcname($query);

}//获取一条记录,前置条件通过资源获取一条记录public function getFormSource($query, $type ="assoc") {if(!in_array($type, array("assoc", "array", "row"))) {

die("mysqli_query error");

}

$funcname="mysqli_fetch_" . $type;return$funcname($query);

}//获取多条数据，二维数组public functiongetAll($sql) {

$query= $this->query($sql);

$list=array();while ($r = $this->getFormSource($query)) {

$list[] =$r;

}return$list;

}public function selectAll($table, $where, $fields = '*', $order = '', $skip = 0, $limit = 1000) {if (is_array($where)) {

foreach ($where as $key =>$val) {if(is_numeric($val)) {

$condition= $key . '='. $val;

}else{

$condition= $key . '=\"' . $val . '\"';

}

}

}else{

$condition= $where;

}if (!empty($order)) {

$order = " order by " . $order;

}

$sql= "select $fields from $table where $condition $orderlimit $skip,$limit";

$query= $this->query($sql);

$list=array();while ($r = $this->getFormSource($query)) {

$list[] =$r;

}return$list;

}/**

* 定义添加数据的方法

* @param string $table 表名

* @param string orarray $data [数据]

* @return int 最新添加的id*/

public function insert($table, $data) {//遍历数组，得到每一个字段和字段的值

$key_str= '';

$v_str= '';

foreach ($dataas $key =>$v) {// if(empty($v)){//die("error");//}//$key的值是每一个字段s一个字段所对应的值

$key_str .= $key . ',';

$v_str .= "'$v',";

}

$key_str= trim($key_str, ',');

$v_str= trim($v_str, ',');//判断数据是否为空

$sql= "insert into $table ($key_str) values($v_str)";

$this->query($sql);//返回上一次增加操做产生ID值return $this->getInsertid();

}/** 删除一条数据方法

* @param1 $table, $where=array('id'=>'1') 表名 条件

* @return 受影响的行数*/

public function deleteOne($table, $where) {if (is_array($where)) {

foreach ($where as $key =>$val) {

$condition= $key . '='. $val;

}

}else{

$condition= $where;

}

$sql= "delete from $table where$condition";

$this->query($sql);//返回受影响的行数return mysqli_affected_rows($this->link);

}/** 删除多条数据方法

* @param1 $table, $where 表名 条件

* @return 受影响的行数*/

public function deleteAll($table, $where) {if (is_array($where)) {

foreach ($where as $key =>$val) {if(is_array($val)) {

$condition= $key . 'in (' . implode(',', $val) . ')';

}else{

$condition= $key . '='. $val;

}

}

}else{

$condition= $where;

}

$sql= "delete from $table where$condition";

$this->query($sql);//返回受影响的行数return mysqli_affected_rows($this->link);

}/**

* [修改操作description]

* @param [type] $table [表名]

* @param [type] $data [数据]

* @param [type] $where [条件]

* @return [type]*/

public function update($table, $data, $where, $limit = 0) {//遍历数组，得到每一个字段和字段的值

$str = '';

foreach ($dataas $key =>$v) {

$str .= "$key='$v',";

}

$str = rtrim($str, ',');if (is_array($where)) {

foreach ($where as $key =>$val) {if(is_array($val)) {

$condition= $key . 'in (' . implode(',', $val) . ')';

}else{

$condition= $key . '='. $val;

}

}

}else{

$condition= $where;

}if(!empty($limit)) {

$limit=" limit " . $limit;

}else{

$limit= '';

}//修改SQL语句

$sql= "update $table set $str where$condition $limit";

$this->query($sql);//返回受影响的行数return mysqli_affected_rows($this->link);

}/**

* [获取字段可否为空]

* @param [string] $table [description]

* @return [array] [description]*/

public function getField_nullable($table) {

$sql= "select COLUMN_NAME,IS_NULLABLE from information_schema.COLUMNS where table_name='$table'";

$res= $this->getAll($sql);

$arr=array();

foreach ($resas$value) {if ($value['IS_NULLABLE'] == 'NO') {

$arr[$value['COLUMN_NAME']] =false;continue;

}

$arr[$value['COLUMN_NAME']] =true;

}return$arr;

}/**

* [获取字段数据类型]

* @param [string] $table [description]

* @return [array] [description]*/

public function getField_dataType($table) {

$sql= "select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where table_name='$table'";

$res= $this->getAll($sql);

$arr=array();

foreach ($resas$value) {

$arr[$value['COLUMN_NAME']] = $value['DATA_TYPE'];

}return$arr;

}

}