使用 PDO 连接到MySQL数据库

一 实例化pdo对象

1. $dsn = “mysql:dbname=test;host=127.0.0.1”;
2. $pdo = new PDO($dsn,‘root’,‘root’);

二 数据查询

1、如果不根据用户传过来的值进行操作,可以直接query sql

1. $dsn = “mysql:dbname=test;host=127.0.0.1”;
2. $pdo = new PDO($dsn,‘root’,‘root’);
3. $sql = “select * from student”;
4. $stmt = $pdo->query($sql);
5. var_dump($stmt->fetchAll(2));

数据是可以正常显示的

array (size=39)
  0 => 
    array (size=8)
      'id' =>  '1' (length=1)
      'name' =>  '周更生' (length=9)
      'sex' =>  '男' (length=3)
      'age' =>  '24' (length=2)
      'edu' =>  '大专' (length=6)
      'salary' =>  '5000.00' (length=7)
      'bonus' =>  '200.00' (length=6)
      'city' =>  '山东省' (length=9)
  1 => 
    array (size=8)
      'id' =>  '2' (length=1)
      'name' =>  '王小平' (length=9)
      'sex' =>  '男' (length=3)
      'age' =>  '28' (length=2)
      'edu' =>  '大专' (length=6)
      'salary' =>  '500.00' (length=6)
      'bonus' =>  '200.00' (length=6)
      'city' =>  '陕西省' (length=9)
  2 => 
    array (size=8)
      'id' =>  '3' (length=1)
      'name' =>  '周改娟' (length=9)
      'sex' =>  '女' (length=3)
      'age' =>  '28' (length=2)
      'edu' =>  '大专' (length=6)
      'salary' =>  '8000.00' (length=7)... (size=39) 0 => array (size=8) 'id' => '1' (length=1) 'name' => '周更生' (length=9) 'sex' => '男' (length=3) 'age' => '24' (length=2) 'edu' => '大专' (length=6) 'salary' => '5000.00' (length=7) 'bonus' => '200.00' (length=6) 'city' => '山东省' (length=9) 1 => array (size=8) 'id' => '2' (length=1) 'name' => '王小平' (length=9) 'sex' => '男' (length=3) 'age' => '28' (length=2) 'edu' => '大专' (length=6) 'salary' => '500.00' (length=6) 'bonus' => '200.00' (length=6) 'city' => '陕西省' (length=9) 2 => array (size=8) 'id' => '3' (length=1) 'name' => '周改娟' (length=9) 'sex' => '女' (length=3) 'age' => '28' (length=2) 'edu' => '大专' (length=6) 'salary' => '8000.00' (length=7)...

2、sql中带变量进行查询

1. $id = 5;
2. $pdo = new \PDO(‘mysql:host=127.0.0.1;dbname=test’,‘root’,‘123456’);
3. //查询操作
4. $stmt = $pdo->query(“select * from user where id ='{$id}’ limit 1”);
5. $data = $stmt->fetch(2);
6. var_dump($data);

3、 如果是根据用户传过来的值进行查询,为了防止sql注入,就需要预处理之后再进行查询

1. $dsn = “mysql:dbname=test;host=127.0.0.1”;
2. $pdo = new PDO($dsn,‘root’,‘root’);
3. $name = ‘孙娟’;//这里的$name是接收用户传过来的值,这里我们为了简洁,直接定位
4. $sql = “select * from student where name=:name”;
5. $stmt = $pdo->prepare($sql);
6. $stmt->execute(array(‘name’=>$name));
7. var_dump($stmt->fetch(2));

结果同样可以正常信息显示:

array (size=8)
  'id' =>  '6' (length=1)
  'name' =>  '孙娟' (length=6)
  'sex' =>  '女' (length=3)
  'age' =>  '24' (length=2)
  'edu' =>  '大本' (length=6)
  'salary' =>  '12300.00' (length=8)
  'bonus' =>  '1080.00' (length=7)
  'city' =>  '北京市' (length=9) (size=8) 'id' => '6' (length=1) 'name' => '孙娟' (length=6) 'sex' => '女' (length=3) 'age' => '24' (length=2) 'edu' => '大本' (length=6) 'salary' => '12300.00' (length=8) 'bonus' => '1080.00' (length=7) 'city' => '北京市' (length=9)

三 我们需要知道的连接mysql数据库的三种方式及区别

1、pdo

pdo操作mysql数据库虽然效率相较于mysqli会稍微低了一点点,但胜在扩展性强,以后换数据库可以很方便的进行迁移,代码几乎不用改动,所以极其推荐使用pdo连接操作数据库

2、mysqli

mysqli同样可以实现连接并操作mysql数据库,同样和pdo一样,也支持预处理,并且单纯从效率上来说,好像还比pdo稍微好了那么一点,但缺点是它只能连接mysql数据库,扩展性太差,一旦以后更换数据库,很多代码都需要重写

3、mysql

mysql直接连接数据库,现在官方都把mysql函数废弃了,你还在执着吗? 第一它只支持mysql数据库,可扩展性太差.第二,不支持预处理,安全问题堪忧,所以,千万不要使用mysql连接数据库方式,改用pdo或者mysqli吧