Complete instance of stored procedure extension class for thinkPHP5 framework stored procedure bug override
- 2021-10-15 10:11:38
- OfStack
This article illustrates a stored procedure extension class for bug rewriting of the thinkPHP5 framework stored procedure. Share it for your reference, as follows:
Recent use of tp5 framework to retrieve stored procedures found bug, used for reference a number of official functions, and found an mysqli class to re-write the stored procedures an extension class, the following two classes directly placed project extend directory stored (this folder name please name according to personal habits) directory, it is important to note that the class increases namespace
namespace stored
Just OK.
1. mysqli class, which is directly found on the Internet, add one
getAllData()
To get multiple datasets of stored procedures
<?php
namespace stored;
/*
掌握满足单例模式的必要条件
(1)私有的构造方法-为了防止在类外使用new关键字实例化对象
(2)私有的成员属性-为了防止在类外引入这个存放对象的属性
(3)私有的克隆方法-为了防止在类外通过clone成生另1个对象
(4)公有的静态方法-为了让用户进行实例化对象的操作
*/
class mysqli{
//私有的属性
private static $dbcon=false;
private $host;
private $port;
private $user;
private $pass;
private $db;
private $charset;
private $link;
//私有的构造方法
protected function __construct($config=array()){
header('content-type:text/html;charset=utf-8');
$this->host = $config['hostname'] ? $config['hostname'] : 'localhost';
$this->port = $config['hostport'] ? $config['hostport'] : '3306';
$this->user = $config['username'] ? $config['username'] : 'root';
$this->pass = $config['password'] ? $config['password'] : 'root';
$this->db = $config['database'] ? $config['database'] : 'zhijian';
$this->charset=isset($config['charset']) ? $config['charset'] : 'utf8';
//连接数据库
$this->db_connect();
//选择数据库
$this->db_usedb();
//设置字符集
$this->db_charset();
}
//连接数据库
private function db_connect(){
$this->link=mysqli_connect($this->host,$this->user,$this->pass);
if(!$this->link){
echo "数据库连接失败<br>";
echo "错误编码".mysqli_errno($this->link)."<br>";
echo "错误信息".mysqli_error($this->link)."<br>";
exit;
}
}
//设置字符集
private function db_charset(){
mysqli_query($this->link,"set names {$this->charset}");
}
//选择数据库
private function db_usedb(){
mysqli_query($this->link,"use {$this->db}");
}
//私有的克隆
private function __clone(){
die('clone is not allowed');
}
//公用的静态方法
public static function getIntance(){
if(self::$dbcon==false){
self::$dbcon=new self;
}
return self::$dbcon;
}
//执行sql语句的方法
public function query($sql){
$res=mysqli_query($this->link,$sql);
if(!$res){
echo "sql语句执行失败<br>";
echo "错误编码是".mysqli_errno($this->link)."<br>";
echo "错误信息是".mysqli_error($this->link)."<br>";
}
return $res;
}
//打印数据
public function p($arr){
echo "<pre>";
print_r($arr);
echo "</pre>";
}
public function v($arr){
echo "<pre>";
var_dump($arr);
echo "</pre>";
}
//获得最后1条记录id
public function getInsertid(){
return mysqli_insert_id($this->link);
}
/**
* 查询某个字段
* @param
* @return string or int
*/
public function getOne($sql){
$query=$this->query($sql);
return mysqli_free_result($query);
}
//获取1行记录,return array 1维数组
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);
}
//获取1条记录,前置条件通过资源获取1条记录
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);
}
//获取多条数据，2维数组
public function getAll($sql){
$query=$this->query($sql);
$list=array();
while ($r=$this->getFormSource($query,"row")) {
$list[]=$r;
}
return $list;
}
/**
* 定义添加数据的方法
* @param string $table 表名
* @param string orarray $data [数据]
* @return int 最新添加的id
*/
public function insert($table,$data){
//遍历数组，得到每1个字段和字段的值
$key_str='';
$v_str='';
foreach($data as $key=>$v){
if(empty($v)){
die("error");
}
//$key的值是每1个字段s1个字段所对应的值
$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);
//返回上1次增加操做产生ID值
return $this->getInsertid();
}
/*
* 删除1条数据方法
* @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){
//遍历数组，得到每1个字段和字段的值
$str='';
foreach($data as $key=>$v){
$str.="$key='$v',";
}
$str=rtrim($str,',');
//修改SQL语句
$sql="update $table set $str where $where";
$this->query($sql);
//返回受影响的行数
return mysqli_affected_rows($this->link);
}
/**
* @func: 获取存储过程多条数据集
* @author: bieanju
* @return: array
* @createtime: 2017-12-25
*/
public function getAllData($sql){
if (mysqli_multi_query($this->link,$sql)) {
do {
if ($result = mysqli_store_result($this->link)) {
while ($row = mysqli_fetch_assoc($result)) {
$list[] = $row;
}
/*必须释放*/
mysqli_free_result($result);
}else{
return false;
}
/*mysqli_next_result($this->link) && mysqli_more_results($this->link)*/
} while (mysqli_next_result($this->link) && mysqli_more_results($this->link));
} else {
return false;
}
return $list;
}
}
?>
2. Stored procedure calls extended class library:
<?php
/**
* Handle classes for stored procedures
* @author: bieanju
* @createtime: 2017-12-21
*/
namespace stored;
use think\Config;
class procs extends mysqli{
public $mysqli;
/*
* Stored procedure data parameters
* */
protected $data = [];
/*
* Execute statement
* */
protected $sql = '';
public function __construct($type = "mysqli"){
$config = C(" Stored procedure library configuration parameters ");
if($type == "mysql"){
$config = Config::get('database');
}
$this->mysqli = new mysqli($config);
}
/**
* Assemble the final according to the parameter binding SQL Statement Easy to debug
* @access public
* @param string $sql With parameter binding sql Statement
* @param array $bind Parameter binding list
* @return string
*/
private function getRealSql($sql, array $bind = [])
{
foreach ($bind as $key => $val) {
$value = is_array($val) ? $val[0] : $val;
$value = is_string($val) ? "'{$val}'" : $val;
// Judgment placeholder
$sql = is_numeric($key) ?
substr_replace($sql, $value, strpos($sql, '?'), 1) :
str_replace(
[':' . $key . ')', ':' . $key . ',', ':' . $key . ' '],
[$value . ')', $value . ',', $value . ' '],
$sql . ' ');
}
return rtrim($sql);
}
/**
* @func: Stored procedure executes and gets a dataset
* @author: bieanju
* @return: boolean
* @createtime: 2017-12-22
*/
protected function procs(){
$procedure = in_array(strtolower(substr(trim($this->sql), 0, 4)), ['call', 'exec']);
// Parameter binding
if ($procedure) {
$sql = $this->getRealSql($this->sql,$this->data);
return $this->mysqli->getAllData($sql);
}
return false;
}
/**
* @func: Stored procedure data
* @author: bieanju
* @return: array
* @createtime: 2017-12-22
*/
public function data($data = [])
{
$this->data = $data;
return $this;
}
/**
* @func: Stored procedure sql
* @author: bieanju
* @return: array
* @createtime: 2017-12-22
*/
public function sql($sql = '')
{
$this->sql = $sql;
return $this;
}
/**
* Use DEMO
*/
public function demo(){
return $this->sql("call demo(?,?,?,?,?,?)")->procs();
}
}
?>
3. Use demo in the final project:
use stored\procs;
/* Use use After loading 1 Stored procedure class under step instantiation */
$this->procs = new procs("mysqli");
/* No. 1 2 Step adjustment demo Method and get data */
//$data The parameter passed to the stored procedure placeholder must be array|[ ]
$this->procs->data($data)->demo();
ok is not very simple to call, and the data set of multiple stored procedures is obtained!
Readers who are interested in thinkPHP can check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "codeigniter Introduction Tutorial", "CI (CodeIgniter) Framework Advanced Tutorial", "Zend FrameWork Framework Introduction Tutorial" and "PHP Template Technology Summary".
I hope this article is helpful to the PHP programming based on ThinkPHP framework.