这段时间学习了pdo clss 等知识,pdo操作数据库 对我来说是崭新的，因此，这几天尝试对原来的程序文件进行改造，选几个文件代码，权且当作业吧。
1、自定义通用操作数据库类
实例
<?php
/**
* 通用方法类
*/
class tyFangfa
{
//数据库连接私有属性
private $dsn = 'mysql:host=jdfschool.mysql.rds.aliyuncs.com:3306; dbname=jdfschool;charset=utf8';
private $dsn_web = 'mysql:host=jdfschool.mysql.rds.aliyuncs.com:3306; dbname=yis;charset=utf8';
private $dsn_hz='mysql:host=jdfschool.mysql.rds.aliyuncs.com:3306; dbname=hezong;charset=utf8';
private $user="testuser";
private $user_web="testuser";
private $password="1jcsxdl@";
private $appid = "wx3c26eae6a8366d08";
private $appsecret = "f93c66d27207a3dae89ef49205dd9fbf";
public static $group_name='宜昌金东方教育集团';
//构造方法
function __construct($dsn='',$user='',$password='')
{
if($dsn!=''){
$this->dsn=$dsn;
}
if($user!=''){
$this->user=$user;
}
if($password!=''){
$this->password=$password;
}
}
//常用方法
//返回pdo连接对象
public function getpdo(){
return new PDO($this->dsn,$this->user,$this->password);
}
// 执行sql语句
// 参数： sql预执行语句 para: 参数数组
// 返回查询结果集(select)或影响行数(update,delete,insert)
public function pdo_sql($sql,array $para){
$pdo=new PDO($this->dsn,$this->user,$this->password);
$stmt = $pdo->prepare($sql);
$stmt->execute( $para );
if(stripos($sql,'select')>-1){
return $stmt->fetchAll();
}else{
return $stmt->rowCount();
}
$stmt=null;
$pdo=null;
}
// 获得随机报名号
public function getrandchr() {
$pdo=new PDO($this->dsn,$this->user,$this->password);
$sql = "SELECT * FROM `randchr` limit 1 ";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row=$stmt->fetch();
$stmt = $pdo->prepare("delete from randchr where id=:id");
$stmt->execute([$row['id']]);
return $row['randchr'];
$stmt=null;
$pdo=null;
}
// 获得指定日期的学年 返回记录对象
public function getxuenian($dat = '') {
$pdo=new PDO($this->dsn,$this->user,$this->password);
$dat = $dat == '' ? date('Y-m-d') : $dat;
$sql = "select xuenian,jiaoxue_xuenian,begin,end,xuenian_name from jdf_xuenian";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row=$stmt->fetch()) {
if ((strtotime($dat) > strtotime($row['begin'])) && (strtotime($dat) < strtotime($row['end']))) {
return $row;
break;
}
}
$stmt=null;
$pdo=null;
}
// //修改权限 $order:位置；$key：权限值；$type(bool)：加/减
public function changkey($userid, $order, $key, $type) {
$pdo=new PDO($this->dsn,$this->user,$this->password);
$sql = "select keylist from jdf_users_info where userid=:userid";
$stmt = $pdo->prepare($sql);
$stmt->execute(['userid'=>$userid]);
$keylist=$stmt->fetch()[0];
$keys = explode(',', $keylist);
$chagekey = ( $keys[$order]);
$chagekey = $type ? ($chagekey | ($key)) : (($chagekey) & (~($key)));
$keys[$order] = $chagekey;
$key_list = "";
for ($i = 0; $i < count($keys); $i++) {
$key_list .= $keys[$i] . ",";
}
$key_list = substr($key_list, 0, strlen($key_list) - 1);
$sql = "update jdf_users_info set keylist=:key_list where userid=:userid";
$stmt = $pdo->prepare($sql);
$stmt->execute(['key_list'=>$key_list,'userid'=>$userid]);
return $key_list;
$stmt = null;
$pdo = null;
}
//获取留言数据
public function getlybyopenid($schoolid = 5, $openid = '',$offset = 0, $pagesize = 10) {
$pdo=new PDO($this->dsn_web,$this->user_web,$this->password);
$sql = $openid == '' ? "select * from yis_message where `from` =:schoolid " : "select * from yis_message where openid=:openid and `from`=:schoolid ";
$sql .= " and pass=0 order by id desc limit {$offset},{$pagesize}";
$stmt = $pdo->prepare($sql);
$stmt->execute($openid == '' ?['schoolid'=>$schoolid]:['schoolid'=>$schoolid,'openid'=>$openid]);
return $stmt->fetchAll();
$stmt=null;
$pdo=null;
}
//获取留言沉底数据
public function getlypass($schoolid = 5,$offset = 0, $pagesize = 10) {
$pdo=new PDO($this->dsn_web,$this->user_web,$this->password);
$sql = "select * from yis_message where `from` =:schoolid and pass=1 order by id desc limit {$offset},{$pagesize}";
$stmt = $pdo->prepare($sql);
$stmt->execute(['schoolid'=>$schoolid]);
return $stmt->fetchAll();
$stmt=null;
$pdo=null;
}
}
?>
2、测试代码
实例
<?php
require_once 'tyFangfa.php';
header("Content-Type: text/html; charset=utf-8");
$ty=new tyFangfa();
$class='tyFangfa';
$sql="select id,name,userid,schoolid,partment from jdf_users_info where schoolid=:schoolid limit 1,10";
$pdo=$ty->getpdo();
$stmt=$pdo->prepare($sql);
$stmt->execute(["schoolid"=>2]);
echo "<hr /><h3>测试 getpdo </h3>";
while($row=$stmt->fetch()){
echo $row['id'],",",$row['name'],',',$row['schoolid'],"<br />";
}
echo "<hr />";
$stmt=null;
$pdo=null;
echo "<h3>测试getrandchr()</h3>";
echo $class::$group_name," 报名随机号:",$ty->getrandchr(),"<hr />";
echo "<h3>测试 getxuenian()</h3>";
echo "现在是:" ,$ty->getxuenian()[4]."<hr />";
echo "<h3>测试 pdo_sql()</h3>";
$sql="insert jdf_xuenian set xuenian=:xuenian,jiaoxue_xuenian=:jiaoxue_xuenian,xuenian_name=:xuenian_name,begin=:begin,end=:end";
$para=["xuenian"=>'2024',"jiaoxue_xuenian"=>'2024A',"xuenian_name"=>'2024年秋季学期',"begin"=>'2024-08-04',"end"=>'2025-02-04'];
echo "成功插入了".$ty->pdo_sql($sql,$para)."条记录 <br />" ;
$sql="update jdf_users_info set schoolid=:schoolid where userid=:userid";
$para=["schoolid"=>'7',"userid"=>'13872520152'];
echo "成功修改了".$ty->pdo_sql($sql,$para)."条记录 <br />" ;
$sql="select id,name,schoolid,times from jdf_users_info where times>:times and schoolid=:schoolid";
$para=["times"=>80,"schoolid"=>1];
$rst=$ty->pdo_sql($sql,$para);
echo "查询结果如下:<br />";
foreach ($rst as $info) {
echo $info['id'],",",$info['name'],',',$info['schoolid'],",",$info['times'],"<br />";
}
echo "<hr />";
echo "<h3>测试 getlybyopenid()</h3>";
$rst=$ty->getlybyopenid(5);
echo "查询结果如下:<br />";
foreach ($rst as $value) {
echo $value['name'],",",date('Y-m-d H:i:s',$value['time']),",",$value['title'],",",$value['content'],"<br />";
}
echo "<h3>测试 getlypass()</h3>";
$rst=$ty->getlypass(5,1,5);
echo "查询结果如下:<br />";
foreach ($rst as $value) {
echo $value['name'],",",date('Y-m-d H:i:s',$value['time']),",",$value['title'],",",$value['content'],"<br />";
}
echo "<hr /><h3>测试 changkey()</h3>";
echo $ty->changkey('13687220600',4,63,true),"<br />";
echo $ty->changkey('13687220600',5,15,true),"<br />";
echo $ty->changkey('13687220600',4,2,false),"<br />";
echo $ty->changkey('13687220600',5,4,false),"<br />";
?>
运行实例 »
点击 "运行实例" 按钮查看在线实例运行实例 »
点击 "运行实例" 按钮查看在线实例
3 运行结果：
