php pdo操作mysql

pdo链接MySQL数据库

/1.php

<?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 Database\PdoMysql;

//数据库配置
$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')
//        ->limit(1,5)
//        ->where('id>5')
//        ->order("id desc")
//        ->get();
//    echo "<pre>";
//    var_dump($r);

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

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

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

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