![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Correction status：qualified
Teacher's comments：创建数组请使用: [...], 不要再用array()函数, 这是非常过时的语法了
Collection
Collection
调试效果图
调试页代码截图
新增
删除
更新
查找
测试页代码 test.php：
<?php
/**
* Created by PhpStorm.
* User: A
* Date: 2019-08-03
* Time: 03:24
*/
include "./Db.php";
$db=new Db();
//功能测试
//1、增 新增一条数据 待插入的单条新数据 $data_1 = array("name.=.破军","sex.=.0","age.=.88","salary.=.7770");
echo "<br>";
$data_1 = array("name.=.破军","sex.=.0","age.=.88","salary.=.7770");
$res=$db->table("staff")->insert($data_1);
echo var_dump($res);
if($res){echo "新增单条数据成功";}else{echo"新增单条数据失败";};
//*************************************************************************************
//2、增 新增多条数据 多条新数据 $data_2 = array(array("name.=.风行者","age.=.38","salary.=.7800"), array("name.=.夜语","age.=.32","salary.=.7900"));
echo "<br>";
$data_2 = array(array("name.=.风行者","age.=.38","salary.=.7800"), array("name.=.夜语","age.=.32","salary.=.7900"));
$res= $db->table("staff")->insert($data_2);
echo var_dump($res);
if($res){echo "新增多条数据成功";}else{echo"新增多条数据失败";};
//************************************************************************************
//3、删除 删除一条记录 $where_1 = array("id.=.203");
echo "<br>";
$where_1 = array("id.=.203");
$res= $db->table("staff")->where($where_1)->delete();
if($res){echo "删除一条数据成功";}else{echo"删除一条数据失败";};
//************************************************************************************
//4、改 更新一条记录 $where_2 = array("salary.=.7900"); 更新为 ：$data_1 =array("salary.=.9123");
echo "<br>";
$where_2 = array("salary.=.7900");
$data_1 =array("salary.=.9123");
$res= $db->table("staff")->where($where_2)->update($data_1);
if($res){echo "更新数据成功";}else{echo"更新数据失败";};
//************************************************************************************
//5、无条件查询 table("表名") field("显示字段列表") select() Db类中的查询方法
echo "<br>";
$res = $db->table("staff")->field("name,age,sex")->select();
foreach ($res as $val){
echo "<pre>";
print_r($val);
}
//************************************************************************************
//5、复合条件查询 条件 $where_4 = array("age.>.20","name.LIKE.%武%");
echo "<br>";
$where_4 = array("age.>.20","name.LIKE.%武%");
$res = $db->table("staff")->field("name,age,sex,salary")->where($where_4)->select();
foreach ($res as $val){
echo "<pre>";
print_r($val);
}点击 "运行实例" 按钮查看在线实例
Db类的定义代码
<?php
/**
* Created by PhpStorm.
* User: A
* Date: 2019-08-03
* Time: 01:24
*/
/* table()-> |field()->insert(array $data) 增 测试完成
-> |where(array $where_data)->delete(); 删 测试完成
-> |where(array $where)->update(array $data） 改 测试完成
-> |field()->where(array $where_data)->select() 查 测试完成
table( 参数类型 str ) 样例: table('user') 数据库语句中显示为: `user`
field( 参数类型 str ) 样例: field("name,sex,age,salary") 数据库语句中显示为:`name`,`sex`,`age`,`salary`
insert( 参数类型 array) 样例: insert( array("name.=.武破天","sex.=.0") )
where( 参数类型 array) 样例: where( array("age.>.25","name.link.武%","salary.in.(3000,8000)") );
array $data 属性数据样式: array("'小龙女',1,32,8000") 或 array("name.=.风尊者","age.=.29","salary.=.9000")
array $where 条件数据样式: array("`age`= 25","`name`= '武松'") 或 array("salary in (3000,9000)","`name` like '武%'")
*/
class Db{
private $sql = '';
private $table_name = '';
private $field = '*';
private $where = [];
private $where_or = [];
private $where_data =[];
private $where_data_or=[];
private $bind_data = [];
private $bind_values = [];
private $group = '';
private $having ='';
private $order = '';
private $limit = '';
//数据库连接 --构造函数
public function __construct()
{
require "./config.php";
try{
$this->pdo=new PDO($dns,$username,$password);
echo '数据库连接OK';
}catch(PDOException $e){
die('数据库连接失败'.$e->getMessage());
}
return $this;
}
//参数初始化重置
public function var_clear(){
$this->sql = '';
$this->table_name = '';
$this->field = '*';
$this->where = [];
$this->where_or = [];
$this->where_data =[];
$this->where_data_or =[];
$this->bind_data = [];
$this->bind_values =[];
$this->group = '';
$this->having ='';
$this->order = '';
$this->limit = '';
}
//数据库查询字段参数赋值获取 table('str参数')---------------------------------
public function table($table)
{
$this->table_name='`'.$table.'`';
return $this;
}
//指定查询字段 field('str参数')
public function field($field)
{
if($field=='*'){
$this->field = $field;
}else{
$fields= explode (',',$field);
$field_str='';
foreach($fields as $var)
{
$field_str .= '`'.$var.'`,';
}
$fields = rtrim($field_str,',');
$this->field = $fields;
}
return $this;
}
//指定查询条件 array("age.>.25","name.=.武松")
public function where(array $where)
{
$this->where = $where;
return $this;
}
//or条件查询
public function where_or(array $where)
{
$this->where_or = $where;
return $this;
}
//分组-简单字段分组
public function group($field)
{
$this->group = '`'.$field.'`';
return $this;
}
//分组-条件分组
public function having($having)
{
$this->having = $having;
return $this;
}
// 指定排序
public function order($order)
{
$this->order = $order;
return $this;
}
// 指定查询数量显示
public function limit($limit)
{
$this->limit = $limit;
return $this;
}
//数据库查询条件字段参数赋值获取 结束 ---------------------------------
// 一.自定义常用方法
//查询1条数据，多用于登录验证
public function item(){
$this->limit(1);
$this->_build_sql('select');
$stmt = $this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
$stmt->execute();
$item = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $item ? $item[0] : false;
}
// 二 : 常规数据操作
//1.新增操作-------------------------------------------------------------
public function insert(array $data)
{
$this->_build_sql('insert',$data);
$stmt=$this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
if(count($data)==count($data,1)){
if($stmt->execute())
{
$this->var_clear();
return true;
}else{
return false;
}
}else{
//绑定的属性名 : :varName
$data =$this->bind_data;
$values = $this->bind_values;
foreach ($data as $k => $var)
{
foreach($values as $val)
{
$tag = ltrim($val,':');
// $val = :属性名 $data[$k][$tag] 对应的属性值
$stmt->bindValue($val, $data[$k][$tag]);
}
if(!$stmt->execute()){ return false; }
}
$this->var_clear();
return true;
}
}
//2.删除操作 -------------------------------------------------------------
public function delete()
{
$this->_build_sql('delete');
$stmt=$this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
if($stmt->execute())
{
//echo '删除数据成功';
$this->var_clear();
return true;
}else{
return false;
}
}
//3.更新操作-------------------------------------------------------------------
public function update($data)
{
$this->_build_sql('update',$data);
$stmt=$this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
if($stmt->execute())
{
$this->var_clear();
return true;
}else{
return false;
}
}
//4.查询操作-------------------------------------------------------------------
public function select()
{
$this->_build_sql('select');
$stmt=$this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
$stmt->execute();
// if($stmt->execute())
// {
// echo '查询数据成功';
// }else{
// echo '查询数据失败';
// }
$res=$stmt->fetchAll(PDO::FETCH_ASSOC);
$this->var_clear();
// echo '<pre>';
// print_r($res);
return $res;
}
//5.统计总数-------------------------------------------------------------------
public function count_rows()
{
$this->_build_sql('count');
$stmt=$this->pdo->prepare($this->sql);
$this->_build_bind_value($stmt);
if($stmt->execute()){
$this->var_clear();
}
else{
echo "数据统计失败7";
}
$res = $stmt->fetchColumn(0);
return $res;
}
// 二 : 构造sql的语句生成
public function _build_sql($type,array $data=[])
{
switch ($type){
case 'insert':
$this->_build_sql_insert($data);
break;
case 'delete':
$this->_build_sql_delete();
break;
case 'update':
$this->_build_sql_update($data);
break;
case 'select':
$this->_build_sql_select();
break;
case 'count':
$this->_build_sql_count();
break;
}
}
//1.增:构造sql_insert生成 数据格式
// array("name.=.破军","sex.=.0","age.=.88","salary.=.7770")
public function _build_sql_insert(array $data)
{
//新增语句: INSERT INTO table_name ( column1,column2) VALUES (val1,val2),(val1,val2)
$insert_fields='';
$insert_values='';
$insert_data=[];
$insert_i_fields='';
$insert_i_values='';
if(count($data,0)==count($data,1))
{ //1条记录新增处理方式 一维数组数据
foreach ($data as $var)
{
$a = strpos($var,'.=');
$b = strrpos($var,'=.');
$insert_field = substr($var,0,$a);
$insert_var = substr($var,$b+2);
$insert_data["$insert_field"]=$insert_var;
$insert_fields .= ' `'.$insert_field.'`,';
$insert_values .=':'.$insert_field.',';
}
$this->bind_data = $insert_data;
$insert_fields=rtrim($insert_fields,',');
$this->field ='('.$insert_fields.')';
$insert_values = rtrim($insert_values,',');
$sql ="INSERT INTO {$this->table_name} {$this->field} VALUES ";
$sql .='('.$insert_values.')';
return $this->sql=$sql;
}else{
//多条记录新增方式，二维数组数据
//array(array("name.=.风行者","age.=.38","salary.=.7800"), array("name.=.夜语","age.=.32","salary.=.7900"));
for($i=0;$i<count($data,0);$i++)
{
foreach($data[$i] as $var)
{
$a = strpos($var,'.=');
$b = strrpos($var,'=.');
$insert_i_field = substr($var,0,$a);
$insert_i_value = substr($var,$b+2);
$insert_data[$i]["$insert_i_field"]=$insert_i_value;
if(!strstr($insert_i_fields,$insert_i_field)){
$insert_i_fields .= ' `'.$insert_i_field.'`,';
}
if(!strstr($insert_i_values,$insert_i_field)){
$insert_i_values .=':'.$insert_i_field.',';
}
}
$insert_fields=rtrim($insert_i_fields,',');
$insert_i_values = rtrim($insert_i_values,',');
}
$this->bind_data=$insert_data;
$this->bind_values=explode(',',$insert_i_values);
$this->field ='('.$insert_fields.')';
$insert_values ='('.$insert_i_values.'),';
$insert_values = rtrim($insert_values,',');
$sql ="INSERT INTO {$this->table_name} {$this->field} VALUES $insert_values";
return $this->sql=$sql;
}
}
//2.删:构造sql_delete生成
public function _build_sql_delete()
{
//删除语句: DELETE FROM table_name WHERE ???
$sql = "DELETE FROM {$this->table_name} ";
$sql .= $this->_build_sql_where();
return $this->sql=$sql;
}
//3.改：构造sql_update生成 数据格式
//条件格式 $where = array("age.>.25","name.link.武%","salary.in.(3000,8000)");
//数据格式 $data = $data_4 =array("salary.=.9800");
public function _build_sql_update(array $data)
{
//更新语句: UPDATE语句模板 UPDATE table_name set column1=value1,column1=value1 WHERE ?
$sql = "UPDATE {$this->table_name} SET ";
$update_str='';
$update_data =[];
foreach ($data as $var)
{
$a = strpos($var,'.=');
$b = strrpos($var,'=.');
$update_field = substr($var,0,$a);
$update_var = substr($var,$b+2);
$update_data["$update_field"]=$update_var;
$update_str .= ' `'.$update_field.'` = :'.$update_field.' ,';
}
$update_str = rtrim($update_str,' ,');
$this->bind_data = $update_data;
$sql .= $update_str;
$sql .= $this->_build_sql_where();
return $this->sql=$sql;
}
//4.查：构造sql_select生成 limit属于最后一个参数
public function _build_sql_select()
{
//查询数据SELECT语句模板 SELECT 字段列表 FROM table1,table2 [WHERE ] [GROUP BY] [ORDER BY] [LIMIT]
$sql="SELECT {$this->field} FROM {$this->table_name}";
if ($this->where){ $sql .= $this->_build_sql_where(); }
if ($this->where_or){ $sql .= $this->_build_sql_where_or(); }
if($this->group){ $sql .=" group by {$this->group}"; }
if($this->having){ $sql .=" having {$this->having}"; }
if ($this->order){ $sql .= " order by {$this->order}";}
if($this->limit){ $sql .= " limit {$this->limit}";}
return $this->sql=$sql;
}
//5.统计sql_count
public function _build_sql_count()
{
$where = $this->_build_sql_where();
$this->sql ="SELECT count({$this->field}) FROM {$this->table_name}{$where}";
return $this->sql;
}
//8.构造sql_where and 语句 生成
//数据的类型:array("age.>.25","name.=.武松","salary.in.(3000,8000),","sex.is.null","age.like.龙%");
public function _build_sql_where()
{
$where_data=[];
$where='';
$sql_where='';
if(empty($this->where)){
$sql_where='';
}else{
foreach ($this->where as $var)
{
$a = strpos($var,'.');
$b = strrpos($var,'.');
$c=$b-$a-1;
$where_field = substr($var,0,$a);
$where_tag = substr($var,$a+1,$c);
$where_var = substr($var,$b+1);
$where_data["w_$where_field"]=$where_var;
$where .= ' AND `'.$where_field.'` '.$where_tag.' :w_'.$where_field;
}
$this->where_data = $where_data;
// echo '<pre>';
// print_r($this->where_data);
// echo '--data<br>';
$where = ltrim($where," AND");
$sql_where .=" WHERE ".$where;
}
// echo $sql_where.'<br>';
return $sql_where;
}
//9.构造sql_where or 语句 生成
public function _build_sql_where_or()
{
$where_data_or = [];
$where_or='';
if(empty($this->where_or)){
$where_or='';
}else{
foreach ($this->where_or as $var)
{
$a = strpos($var,'.');
$b = strrpos($var,'.');
$c=$b-$a-1;
$where_field = substr($var,0,$a);
$where_tag = substr($var,$a+1,$c);
$where_var = substr($var,$b+1);
$where_data_or["r_$where_field"]=$where_var;
$where_or .= ' or `'.$where_field.'` '.$where_tag.' :r_'.$where_field;
}
$this->where_data_or = $where_data_or;
}
return $where_or;
}
//10.参数绑定，常见参数 where条件数组 where_or条件数组 update 更新数组 insert 新增数组
private function _build_bind_value($stmt)
{
if($this->where_data)
{
$where_data = $this->where_data;
foreach($where_data as $key=>$var)
{
// echo $key.'<br>';
// echo $var.'<br>';
$stmt->bindValue(':'.$key,$var);
}
}
if ($this->bind_data)
{
$data =$this->bind_data;
//判断数组是否为多维数组 多为数组数据处理在155行处理
if(count($data,0)==count($data,1)){
foreach($data as $k=>$v)
{
// echo $k.'--';
// echo $v.'<br>';
$stmt->bindValue(':'.$k,$v);
}
}
}
if($this->where_data_or)
{
$where_data_or = $this->where_data_or;
foreach($where_data_or as $key=>$var)
{
$stmt->bindValue(":".$key,$var);
}
}
}
}点击 "运行实例" 按钮查看在线实例
配置文件 confing.php
<?php /** * Created by PhpStorm. * User: A * Date: 2019-08-03 * Time: 01:20 * 说明 数据库连接参数 */ $dns="mysql:host=127.0.0.1;dbname=php"; $username='root'; $password='root';
点击 "运行实例" 按钮查看在线实例
