1、这个类有什么问题，请指教？
2、返回数据库记录集合，php主流的都是返回数组吗，可以返回实体类对象集合吗？
3、PHP流行用三层吗？层与层之间有接口。
<?php
/**
* 数据库操作类，mysqli,单件模式，
*/
class DB
{
private $db_host = "localhost";
private $db_user = "root";
private $db_password = "4445578";
private $db_name = "books";
private $db_encoded = "utf8";
private $db_con = null;
function __construct()
{
//echo "初始化！";
}
//单件模式
static function get_instance()
{
static $instance;
if(!isset($instance)){
$c = __CLASS__;
$instance = new $c;
}
return $instance;
}
public function open()
{
$result = true;
if(!isset($this->db_con))
{
$this->db_con = @new mysqli($this->db_host,$this->db_user,$this->db_password,$this->db_name);
//echo '打开';
if($this->db_con->connect_errno)
{
$this->write_err_log("数据库连接错误。",$this->db_con->connect_errno);
$this->db_con = null;
$result = false;
}else {
$this->db_con->query('SET NAMES '.$this->db_encoded);
//echo '设置编码';
}
}
return $result;
}
//插入,返回插入数量，返回插入自增ID。
public function insert($sql,&$id)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql))
{
$result = $this->db_con->affected_rows;
$id = $this->db_con->insert_id;
}
}
return $result;
}
//更新or删除，返回行数
public function update_or_delete($sql)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql)){
$result = $this->db_con->affected_rows;
}
}
return $result;
}
//查询，返回数组，返回行数
public function select($sql,&$num_rows)
{
$result = null;
if($this->open()){
if($mysqli_stmt = $this->db_con->query($sql)){
$num_rows = $mysqli_stmt->num_rows;
$result = $this->fetch_row($mysqli_stmt);
}
}
return $result;
}
//分页查询，返回数组，返回数量
public function select_paging($sql,$page_num,$page_size,&$amount)
{
$result = null;
if($this->open()){
$num_sql = $this->replace_sql($sql);
if($mysqli_stmt = $this->db_con->query($num_sql)){
$row = $mysqli_stmt->fetch_assoc();
$amount = $row['amount'];
}
if($amount > 0){
if(($page_num * $page_size - $amount) >= $page_size) $page_num = 1;
$sql = $sql.' LIMIT '.($page_num - 1) * $page_size.' , '.$page_size;
if($mysqli_stmt = $this->db_con->query($sql)){
$result = $this->fetch_row($mysqli_stmt);
}
}
}
return $result;
}
private function replace_sql($sql)
{
$pattern = "/SELECT.*FROM/iAU";
return preg_replace ($pattern, "SELECT count(*) as amount FROM", $sql);
}
private function fetch_row($stmt)
{
$result=array();
$i=0;
while ($row=$stmt->fetch_assoc()){
$result[$i++]=$row;
}
return $result;
}
public function close()
{
if(isset($this->db_con))
{
$this->db_con->close();
$this->db_con = null;
//echo '关闭';
}
}
private function write_err_log($message,$err_code)
{
echo '写入错误日志:'.$err_code;
}
}
//$num = DB::get_instance()->update_or_delete
//('DELETE FROM `customers` WHERE `address` like "%3004%";');
//echo "<br/> $num";
//DB::get_instance()->open();
//echo DB::get_instance()->replace_sql("SELECT `isbn` ,`author` ,`title` ,`price` from `books` WHERE `isbn` NOT IN (select isbn from book_reviews) LIMIT 0 , 30");
$num = 0;
$arr = DB::get_instance()->select_paging("SELECT `customerid`, `name`, `address`, `city` FROM `customers` ",5,2,$num);
echo "<br/>";
print_r($arr);
echo "<br/> $num";
?>
10 个解决方案
#1
全部用private，不能外部改写，那写成类有什么意义？其实就是一个实例而已
#2
private $db_host = "localhost";
private $db_user = "root";
private $db_password = "4445578";
private $db_name = "books";
private $db_encoded = "utf8";
这些都是数据库连接的参数，配置好基本没有什么变动，没必要向外部公开啊。
在外部设五个常量，在构造时，将常量赋值给这五个private 变量成员。
这就是一个实例而已，用于操作数据库啊。
#3
define("DB_HOST","localhost");
define("DB_USER","root");
define("DB_PASSWORD","4445578");
define("DB_NAME","books");
define("DB_ENCODED","utf8");
function __construct()
{
if (defined("DB_HOST")) $this->db_host = DB_HOST;
if (defined("DB_USER")) $this->db_user = DB_USER;
if (defined("DB_PASSWORD")) $this->db_password = DB_PASSWORD;
if (defined("DB_NAME")) $this->db_name = DB_NAME;
if (defined("DB_ENCODED")) $this->db_encoded = DB_ENCODED;
}
#4
固死在一个环境使用的类，没错也叫类，但没意义了
试想你的系统升级了，数据库分布在多台机器，不再是localhost；或者多用户了，不再是root；或者多个库了，不仅是books
你这个类就要重写，类重写，由于原来的程序无需传入参数（你固定了，除了SQL还要传参么？），变成要传参数了，那不就全部程序都要重写？这个类不能重用还有意义么？
试想你的系统升级了，数据库分布在多台机器，不再是localhost；或者多用户了，不再是root；或者多个库了，不仅是books
你这个类就要重写，类重写，由于原来的程序无需传入参数（你固定了，除了SQL还要传参么？），变成要传参数了，那不就全部程序都要重写？这个类不能重用还有意义么？
#5
两个问题：
1、楼上几位已说，就是数据库连接的配置，每次换数据库必须改这个类...那么这个类基本没啥意义；
2、什么是单例模式？建议楼主再去查一下...
1、楼上几位已说，就是数据库连接的配置，每次换数据库必须改这个类...那么这个类基本没啥意义；
2、什么是单例模式？建议楼主再去查一下...
#6
修改以后的版本：数据库变动，修改五个常量即可。无线修改类啊！
define("DB_HOST","localhost");
define("DB_USER","root");
define("DB_PASSWORD","4445578");
define("DB_NAME","books");
define("DB_ENCODED","utf8");
class DB
{
private $db_host = "localhost";
private $db_user = "root";
private $db_password = "4445578";
private $db_name = "books";
private $db_encoded = "utf8";
private $db_con = null;
function __construct()
{
if (defined("DB_HOST")) $this->db_host = DB_HOST;
if (defined("DB_USER")) $this->db_user = DB_USER;
if (defined("DB_PASSWORD")) $this->db_password = DB_PASSWORD;
if (defined("DB_NAME")) $this->db_name = DB_NAME;
if (defined("DB_ENCODED")) $this->db_encoded = DB_ENCODED;
}
//单件模式
static function get_instance()
{
static $instance;
if(!isset($instance)){
$c = __CLASS__;
$instance = new $c;
}
return $instance;
}
public function open()
{
$result = true;
if(!isset($this->db_con))
{
$this->db_con = @new mysqli($this->db_host,$this->db_user,$this->db_password,$this->db_name);
//echo '打开';
if($this->db_con->connect_errno)
{
$this->write_err_log("数据库连接错误。",$this->db_con->connect_errno);
$this->db_con = null;
$result = false;
}else {
$this->db_con->query('SET NAMES '.$this->db_encoded);
//echo '设置编码';
}
}
return $result;
}
//插入,返回插入数量，返回插入自增ID。
public function insert($sql,&$id)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql))
{
$result = $this->db_con->affected_rows;
$id = $this->db_con->insert_id;
}
}
return $result;
}
//更新or删除，返回行数
public function update_or_delete($sql)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql)){
$result = $this->db_con->affected_rows;
}
}
return $result;
}
//查询，返回数组，返回行数
public function select($sql,&$num_rows)
{
$result = null;
if($this->open()){
if($mysqli_stmt = $this->db_con->query($sql)){
$num_rows = $mysqli_stmt->num_rows;
$result = $this->fetch_row($mysqli_stmt);
}
}
return $result;
}
//分页查询，返回数组，返回数量
public function select_paging($sql,$page_num,$page_size,&$amount)
{
$result = null;
if($this->open()){
$num_sql = $this->replace_sql($sql);
if($mysqli_stmt = $this->db_con->query($num_sql)){
$row = $mysqli_stmt->fetch_assoc();
$amount = $row['amount'];
}
if($amount > 0){
if(($page_num * $page_size - $amount) >= $page_size) $page_num = 1;
$sql = $sql.' LIMIT '.($page_num - 1) * $page_size.' , '.$page_size;
if($mysqli_stmt = $this->db_con->query($sql)){
$result = $this->fetch_row($mysqli_stmt);
}
}
}
return $result;
}
private function replace_sql($sql)
{
$pattern = "/SELECT.*FROM/iAU";
return preg_replace ($pattern, "SELECT count(*) as amount FROM", $sql);
}
private function fetch_row($stmt)
{
$result=array();
$i=0;
while ($row=$stmt->fetch_assoc()){
$result[$i++]=$row;
}
return $result;
}
public function close()
{
if(isset($this->db_con))
{
$this->db_con->close();
$this->db_con = null;
//echo '关闭';
}
}
private function write_err_log($message,$err_code)
{
echo '写入错误日志:'.$err_code;
}
}
//$num = DB::get_instance()->update_or_delete
//('DELETE FROM `customers` WHERE `address` like "%3004%";');
//echo "<br/> $num";
//DB::get_instance()->open();
//echo DB::get_instance()->replace_sql("SELECT `isbn` ,`author` ,`title` ,`price` from `books` WHERE `isbn` NOT IN (select isbn from book_reviews) LIMIT 0 , 30");
$num = 0;
$arr = DB::get_instance()->select_paging("SELECT `customerid`, `name`, `address`, `city` FROM `customers` ",5,2,$num);
echo "<br/>";
print_r($arr);
echo "<br/> $num";
#7
单例模式是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例类的特殊类。通过单例模式可以保证系统中一个类只有一个实例而且该实例易于外界访问，从而方便对实例个数的控制并节约系统资源。如果希望在系统中某个类的对象只能存在一个，单例模式是最好的解决方案。
没什么不对呀！
没什么不对呀！
#9
单例模式，你这个不能算作单例。
加个这个吧，为什么自己想~
private function __clone(){
}
加个这个吧，为什么自己想~
private function __clone(){
}
#10
您这是伪单例吧，构造函数在不申明方法类型的时候是默认为public的哦，这样的话是完全可以通过$db=new DB();来获取这个实例的，如果要真写成单例模式，需要将构造函数申明为private，且按照@夏之冰雪那样将__clone也private。其实我没搞懂为什么DB类一定要设置成单例？虽然说他是软件设计的一种模式，是否有过度设计的嫌疑？
#1
全部用private，不能外部改写，那写成类有什么意义？其实就是一个实例而已
#2
private $db_host = "localhost";
private $db_user = "root";
private $db_password = "4445578";
private $db_name = "books";
private $db_encoded = "utf8";
这些都是数据库连接的参数，配置好基本没有什么变动，没必要向外部公开啊。
在外部设五个常量，在构造时，将常量赋值给这五个private 变量成员。
这就是一个实例而已，用于操作数据库啊。
#3
define("DB_HOST","localhost");
define("DB_USER","root");
define("DB_PASSWORD","4445578");
define("DB_NAME","books");
define("DB_ENCODED","utf8");
function __construct()
{
if (defined("DB_HOST")) $this->db_host = DB_HOST;
if (defined("DB_USER")) $this->db_user = DB_USER;
if (defined("DB_PASSWORD")) $this->db_password = DB_PASSWORD;
if (defined("DB_NAME")) $this->db_name = DB_NAME;
if (defined("DB_ENCODED")) $this->db_encoded = DB_ENCODED;
}
#4
固死在一个环境使用的类，没错也叫类，但没意义了
试想你的系统升级了，数据库分布在多台机器，不再是localhost；或者多用户了，不再是root；或者多个库了，不仅是books
你这个类就要重写，类重写，由于原来的程序无需传入参数（你固定了，除了SQL还要传参么？），变成要传参数了，那不就全部程序都要重写？这个类不能重用还有意义么？
试想你的系统升级了，数据库分布在多台机器，不再是localhost；或者多用户了，不再是root；或者多个库了，不仅是books
你这个类就要重写，类重写，由于原来的程序无需传入参数（你固定了，除了SQL还要传参么？），变成要传参数了，那不就全部程序都要重写？这个类不能重用还有意义么？
#5
两个问题：
1、楼上几位已说，就是数据库连接的配置，每次换数据库必须改这个类...那么这个类基本没啥意义；
2、什么是单例模式？建议楼主再去查一下...
1、楼上几位已说，就是数据库连接的配置，每次换数据库必须改这个类...那么这个类基本没啥意义；
2、什么是单例模式？建议楼主再去查一下...
#6
修改以后的版本：数据库变动，修改五个常量即可。无线修改类啊！
define("DB_HOST","localhost");
define("DB_USER","root");
define("DB_PASSWORD","4445578");
define("DB_NAME","books");
define("DB_ENCODED","utf8");
class DB
{
private $db_host = "localhost";
private $db_user = "root";
private $db_password = "4445578";
private $db_name = "books";
private $db_encoded = "utf8";
private $db_con = null;
function __construct()
{
if (defined("DB_HOST")) $this->db_host = DB_HOST;
if (defined("DB_USER")) $this->db_user = DB_USER;
if (defined("DB_PASSWORD")) $this->db_password = DB_PASSWORD;
if (defined("DB_NAME")) $this->db_name = DB_NAME;
if (defined("DB_ENCODED")) $this->db_encoded = DB_ENCODED;
}
//单件模式
static function get_instance()
{
static $instance;
if(!isset($instance)){
$c = __CLASS__;
$instance = new $c;
}
return $instance;
}
public function open()
{
$result = true;
if(!isset($this->db_con))
{
$this->db_con = @new mysqli($this->db_host,$this->db_user,$this->db_password,$this->db_name);
//echo '打开';
if($this->db_con->connect_errno)
{
$this->write_err_log("数据库连接错误。",$this->db_con->connect_errno);
$this->db_con = null;
$result = false;
}else {
$this->db_con->query('SET NAMES '.$this->db_encoded);
//echo '设置编码';
}
}
return $result;
}
//插入,返回插入数量，返回插入自增ID。
public function insert($sql,&$id)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql))
{
$result = $this->db_con->affected_rows;
$id = $this->db_con->insert_id;
}
}
return $result;
}
//更新or删除，返回行数
public function update_or_delete($sql)
{
$result = 0;
if($this->open()){
if($this->db_con->query($sql)){
$result = $this->db_con->affected_rows;
}
}
return $result;
}
//查询，返回数组，返回行数
public function select($sql,&$num_rows)
{
$result = null;
if($this->open()){
if($mysqli_stmt = $this->db_con->query($sql)){
$num_rows = $mysqli_stmt->num_rows;
$result = $this->fetch_row($mysqli_stmt);
}
}
return $result;
}
//分页查询，返回数组，返回数量
public function select_paging($sql,$page_num,$page_size,&$amount)
{
$result = null;
if($this->open()){
$num_sql = $this->replace_sql($sql);
if($mysqli_stmt = $this->db_con->query($num_sql)){
$row = $mysqli_stmt->fetch_assoc();
$amount = $row['amount'];
}
if($amount > 0){
if(($page_num * $page_size - $amount) >= $page_size) $page_num = 1;
$sql = $sql.' LIMIT '.($page_num - 1) * $page_size.' , '.$page_size;
if($mysqli_stmt = $this->db_con->query($sql)){
$result = $this->fetch_row($mysqli_stmt);
}
}
}
return $result;
}
private function replace_sql($sql)
{
$pattern = "/SELECT.*FROM/iAU";
return preg_replace ($pattern, "SELECT count(*) as amount FROM", $sql);
}
private function fetch_row($stmt)
{
$result=array();
$i=0;
while ($row=$stmt->fetch_assoc()){
$result[$i++]=$row;
}
return $result;
}
public function close()
{
if(isset($this->db_con))
{
$this->db_con->close();
$this->db_con = null;
//echo '关闭';
}
}
private function write_err_log($message,$err_code)
{
echo '写入错误日志:'.$err_code;
}
}
//$num = DB::get_instance()->update_or_delete
//('DELETE FROM `customers` WHERE `address` like "%3004%";');
//echo "<br/> $num";
//DB::get_instance()->open();
//echo DB::get_instance()->replace_sql("SELECT `isbn` ,`author` ,`title` ,`price` from `books` WHERE `isbn` NOT IN (select isbn from book_reviews) LIMIT 0 , 30");
$num = 0;
$arr = DB::get_instance()->select_paging("SELECT `customerid`, `name`, `address`, `city` FROM `customers` ",5,2,$num);
echo "<br/>";
print_r($arr);
echo "<br/> $num";
#7
单例模式是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例类的特殊类。通过单例模式可以保证系统中一个类只有一个实例而且该实例易于外界访问，从而方便对实例个数的控制并节约系统资源。如果希望在系统中某个类的对象只能存在一个，单例模式是最好的解决方案。
没什么不对呀！
没什么不对呀！
#8
#9
单例模式，你这个不能算作单例。
加个这个吧，为什么自己想~
private function __clone(){
}
加个这个吧，为什么自己想~
private function __clone(){
}
#10
您这是伪单例吧，构造函数在不申明方法类型的时候是默认为public的哦，这样的话是完全可以通过$db=new DB();来获取这个实例的，如果要真写成单例模式，需要将构造函数申明为private，且按照@夏之冰雪那样将__clone也private。其实我没搞懂为什么DB类一定要设置成单例？虽然说他是软件设计的一种模式，是否有过度设计的嫌疑？