pdo连接mysql数据库(简洁明了)

一 实例化pdo对象

$dsn = "mysql:dbname=test;host=127.0.0.1";
$pdo = new PDO($dsn,'root','root');

 

二 数据查询

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

$dsn = "mysql:dbname=test;host=127.0.0.1";
$pdo = new PDO($dsn,'root','root');
$sql = "select * from student";
$stmt = $pdo->query($sql);
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中带变量进行查询

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

 

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

$dsn = "mysql:dbname=test;host=127.0.0.1";
$pdo = new PDO($dsn,'root','root');
$name = '孙娟';//这里的$name是接收用户传过来的值,这里我们为了简洁,直接定位
$sql = "select * from student where name=:name";
$stmt = $pdo->prepare($sql);
$stmt->execute(array('name'=>$name));
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吧

 

想了解更多可参考 https://www.helloweba.net/php/573.html