分类导航

程序笔记发布时间：2022-07-19 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了php pdo操作mysql，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

pdo链接MySQL数据库

<?php
//链接mysql数据库

$config = [
    'host'=>'127.0.0.1',
    'user'=>'root',
    'password'=>'root',
    'database'=>'php',
    'charset'=>'utf8'
];
$dsn = sprintf("mysql:host=%s;dbname=%s;charset=%s",$config['host'],$config['database'],$config['charset']);

try{
    $pdo = new PDO($dsn,$config['user'],$config['password']);
    var_dump($pdo);
}catch (PDOException $E){
    die($e->getmessage());
}

浏览器访问1.php 查看结果

pdo操作mysql执行语句

/2.php

<?php

//pdo操作执行语句

$config = [
    'host' => '127.0.0.1',
    'user' => 'root',
    'password' => 'root',
    'database' => 'php',
    'charset' => 'utf8'
];
$dsn = sprintf("mysql:host=%s;dbname=%s;charset=%s", $config['host'], $config['database'], $config['charset']);

try {
    $pdo = new PDO($dsn, $config['user'], $config['password']);

    //插入
    $sql = "INSERT INTO student (name,agE) VALUES ('李五','24'),('王小红','26')";
    echo $pdo->exec($sql);

    //更新
    $sql = "updatE student SET age = '34' where id = 5";
    echo $pdo->exec($sql);

    //删除
    $sql = "deletE FROM student where id = 5";
    echo $pdo->exec($sql);

} catch (Exception $E) {
    die($e->getmessage());
}

浏览器访问2.php 修改配置查看插入，更新，删除效果

pdo操作mysql查询语句

/3.php

<?php
//pdo操作查询语句

$config = [
    'host' => '127.0.0.1',
    'user' => 'root',
    'password' => 'root',
    'database' => 'php',
    'charset' => 'utf8'
];
$dsn = sprintf("mysql:host=%s;dbname=%s;charset=%s", $config['host'], $config['database'], $config['charset']);

try {
    $pdo = new PDO($dsn, $config['user'], $config['password']);

    //设置返回结果集类型
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    //$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
    //$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

    //读取
    $sql = "SELEct * from student";
    $query = $pdo->query($sql);
    $rows = $query->fetchAll();
    echo "<pre>";
    print_r($rows);

    //循环结果集
    $sql = "SELEct * from student";
    $query = $pdo->query($sql);
    $rows = $query->fetch();
    while($field = $query->fetch()){
        echo sprintf("name:%s,age:%s<br>",$field['name'],$field['age']);
    }

} catch (Exception $E) {
    die($e->getmessage());
}

浏览器访问3.php 修改配置查看效果

pdo prepare预处理可防止SQL注入

/4.php

<?php
//pdo prepare预处理 可防止SQL注入

$config = [
    'host' => '127.0.0.1',
    'user' => 'root',
    'password' => 'root',
    'database' => 'php',
    'charset' => 'utf8'
];
$dsn = sprintf("mysql:host=%s;dbname=%s;charset=%s", $config['host'], $config['database'], $config['charset']);

try {
    $pdo = new PDO($dsn, $config['user'], $config['password']);

    //设置返回结果集类型
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    //SQL注入
    //http://www.example.com:88/php/pdo/4.php?id=2 or id>1
    //读取
    $sql = "SELEct * from student where id = {$_GET['id']}";
    $query = $pdo->query($sql);
    $rows = $query->fetchAll();
    echo "<pre>";
    print_r($rows);

    //prepare预处理 可防止SQL注入
    $sql = "SELEct * from student where id = :id";
    $query = $pdo->prepare($sql);
    $query->execute([':id'=>$_GET['id']]);
    $rows = $query->fetchAll();
    echo "<pre>";
    print_r($rows);


} catch (Exception $E) {
    die($e->getmessage());
}

浏览器访问4.php 修改配置查看效果

pdo操作mysql的封装

/PdoMysql.php

<?php
namespace Database;

use PDO;

class PdoMysql{

    protected $link;

    protected $options =[
        'table'=>'',
        'fields'=>'*',
        'order'=>'',
        'limit'=>''
    ];

    //初始化
    public function __construct($config)
    {
        $this->connect($config);
    }

    //连接MySQL
    protected function connect(array $config){
        $dsn = sprintf("mysql:host=%s;dbname=%s;charset=%s", $config['host'], $config['database'], $config['charset']);
        $this->link = new PDO($dsn, $config['user'], $config['password']);
    }

    //查询
    public function query(String $sql, array $values=[]){
        $query = $this->link->prepare($sql);
        $query->execute($values);
        return $query->fetchAll();
    }

    //执行
    public function execute(String $sql, array $values = []){
        $query = $this->link->prepare($sql);
        return $query->execute($values);
    }

    //表名
    public function table(String $tablE){
        $this->options['table'] = $table;
        return $this;
    }

    //查询字段
    public function fields(...$fields){
        $fields = '`'. implode('`,`',$fields).'`';
        $this->options['fields'] = $fields;
        return $this;
    }

    //limit
    public function limit(...$limit){
        $limit = implode(',',$limit);
        $this->options['limit'] = " limit " . $limit;
        return $this;
    }

    //order
    public function order(String $order){
        $this->options['order'] = " order by " . $order;
        return $this;
    }

    //条件
    public function where(String $wherE){
        $this->options['where'] = " where " . $where;
        return $this;
    }

    //查询语句生成器
    public function get(){
        //SELEct * from student where order limit

        $sql = "SELEct {$this->options['fields']} from {$this->options['table']} {$this->options['where']} {$this->options['order']} {$this->options['limit']}";
        return $this->query($sql);
    }

    //插入
    public function insert(array $values){
        //insert into student () values ();
        $fields = "`" . implode('`,`',array_keys($values)) . "`";
        $placeholders = implode(',',array_fill(0,count($values),'?'));
        $sql = "insert into {$this->options['table']} ($fields) values ($placeholders)";

        return $this->execute($sql,array_values($values));
    }

    //更新
    public function update(array $values){
        //update student set name=?,age=?
        if(empty($this->options['where'])){
            throw new Exception('update需要条件');
        }
        $fields = implode('=?,',array_keys($values))."=?";
        $sql = "update {$this->options['table']} set ($fields) {$this->options['where']}";
        return $this->execute($sql,array_values($values));
    }

    //删除
    public function delete(){
        //delete fromm student where
        if(empty($this->options['where'])){
            throw new Exception('delete需要条件');
        }
        $sql = "delete from {$this->options['table']} {$this->options['where']}";
        return $this->execute($sql);
    }
}

测试PdoMysql.php封装

/5.php

<?php
include 'PdoMysql.php';
use DatabasePdoMysql;

//数据库配置
$config = [
    'host' => '127.0.0.1',
    'user' => 'root',
    'password' => 'root',
    'database' => 'php',
    'charset' => 'utf8'
];
try{

    $pdo = new PdoMysql($config);


    //查询
    $r = $pdo->query('SELEct * from student where id=:id',[":id"=>4]);
    var_dump($r);

    //执行
//    $sql = "INSERT INTO student SET name=:name,age=:age";
//    $values = ['name'=>'梁梅','age'=>'33'];
//    $r = $pdo->execute($sql,$values);
//    var_dump($r);

//    $r = $pdo->table('student')
//        ->fields('name','age')
//        ->@H_944_991@limit(1,5)
//        ->@H_944_991@where('id>5')
//        ->order("id desc")
//        ->get();
//    @H_944_991@echo "<pre>";
//    var_dump($r);

//    //insert
//    $r = $pdo->table('student')->insert(['name'=>'小花','age'=>'27']);
//    var_dump($r);

//    //update
//    $r = $pdo->table('student')->@H_944_991@where("id=11")->update(['name'=>'小花','age'=>'25']);
//    var_dump($r);

    //delete
//    $r = $pdo->table('student')->@H_944_991@where("id=11")->delete();
//    var_dump($r);
}catch (Exception $E){
    die($e->getmessage());
}

浏览器访问5.php，修改配置查看效果

大佬总结

以上是大佬教程为你收集整理的php pdo操作mysql全部内容，希望文章能够帮你解决php pdo操作mysql所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：mysql php sql while 数据库程序员

上一篇: SpringBoot配置Druid数据库连接池下一篇:.NET Core/.NET5/.NET6 开源项目...

猜你在找的程序笔记相关文章

You can't specify target table 'xxx' for update in FROM clause的解决 2022-07-21
【UNIAPP】上传视频，进度条的前台与后端 2022-07-21
十款代码表白特效，一个比一个浪漫！ 2022-07-04
作业3 2022-07-06
linux系统下部署项目访问报404错误的解决方法 2022-05-30
C++带有指针成员的类处理方式详解 2022-06-07
Linux——（1）基本命令 2022-07-21
JavaScript之正则表达式学习笔记 2019-11-07
Redux源码学习笔记 2019-11-07
Webpack学习笔记 2019-11-07

php pdo操作mysql