PHP连接数据库操作

1.连接数据库操作

PDO:是类的接口，实例化后就可以使用该类的接口了
$db = new PDO('mysql:host=localhost;dbname=phpedu','root','901026yk');

2.插入操作

1.索引插入

<?php
namespace pdo_edu;
use PDO;
$db = new PDO('mysql:host=localhost;dbname=phpedu','root','901026yk');
$sql = 'insert into text values (?,?,?);';
$stmt = $db->prepare($sql);
$data = [2,1,'yn'];
$stmt->execute($data);

2.关联插入

<?php
namespace pdo_edu;
use PDO;
$db = new PDO('mysql:host=localhost;dbname=phpedu','root','901026yk');
$sql = 'insert into text values (:id,:sex,:name);';
$stmt = $db->prepare($sql);
$data = ["id" => 3,"sex" => 1,"name" => '杨康'];
$stmt->execute($data);

3.插入指定类型

直接插入都是字符类型，使用bindValue和bindParame()可以指定类型

<?php
namespace pdo_edu;
use PDO;
$db = new PDO('mysql:host=localhost;dbname=phpedu','root','901026yk');
$sql = 'insert into text values (?,?,?);';
$stmt = $db->prepare($sql);
list($id,$sex,$name) = [4,1,'杨娜'];
$stmt->bindValue(1,$id,PDO::PARAM_INT);
$stmt->bindValue(2,$sex,PDO::PARAM_INT);
$stmt->bindValue(3,$name);
$stmt->execute();

<?php
namespace pdo_edu;
/**
 * $stmt->rowCount()
 * $stmt->errorInfo()
 */
use PDO;
$db = new PDO('mysql:host=localhost;dbname=phpedu','root','901026yk');
$sql = "insert into text values (?,?,?)";
$stmt =  $db->prepare($sql);
$stmt->bindParam(1,$id,PDO::PARAM_INT);
$stmt->bindParam(2,$sex,PDO::PARAM_INT);
$stmt->bindParam(3,$name,PDO::PARAM_STR);
$data = [
    [7,1,'wusong'],
    [8,0,'banjinl'],
    [9,1,'wudal'],
];
foreach ($data as list($id,$sex,$name))
{
    try {
        $stmt->execute();
    }catch (\PDOException $e)
    {
        print_r($e);
    }
}

3.跟新和删除操作

<?php
namespace pdo_edu;
/**
 * $stmt->rowCount()
 * $stmt->errorInfo()
 */
use PDO;
use PDOException;
$db = new PDO('mysql:host=localhost;dbname=phpedu', 'root', '901026yk');
//$sql = <<<SQL
//update text set sex = ? where id = ?;
//SQL;
//if(!stripos($sql,'where'))
//{
//    exit('SQL语句没有where');
//}
$sql = <<<SQL
delete from text where id = ?;
SQL;
$stmt = $db->prepare($sql);
$data = [8];
try {
    $stmt->execute($data);
}catch (PDOException $e)
{
    echo '错误信息：' . $e;
}

4.取操作

1.fetch() + while()

<?php
/**
 * fetch() 和 while()
 */
namespace pdo_edu;
/**
 * $stmt->rowCount()
 * $stmt->errorInfo()
 */
use PDO;
use PDOException;
$db = new PDO('mysql:host=localhost;dbname=phpedu', 'root', '901026yk');
$sql = <<<SQL
select * from text limit ?,?;
SQL;
$stmt = $db->prepare($sql);
//$data = [1,3];// $stmt->execute($data);执行的$data的两个参数都是字符
$stmt->bindValue(1,1,PDO::PARAM_INT);
$stmt->bindValue(2,3,PDO::PARAM_INT);
try {
    $stmt->execute();
    $staff = $stmt->fetch(PDO::FETCH_ASSOC);
    while($staff)
    {
            printf('<pre>%s</pre>', print_r($staff, true));
        $staff = $stmt->fetch(PDO::FETCH_ASSOC);
    }
} catch (PDOException $e) {
    echo '错误信息：' . $e;
}

2.fetchAll + foreach()

<?php
/**
 * fetchAll() 和  foreach()
 */
namespace pdo_edu;
use PDO;
use PDOException;
$db = new PDO('mysql:host=localhost;dbname=phpedu', 'root', '901026yk');
$sql = <<<SQL
select * from text limit ?,?;
SQL;
$stmt = $db->prepare($sql);
$stmt->bindValue(1,1,PDO::PARAM_INT);
$stmt->bindValue(2,3,PDO::PARAM_INT);
try {
    $stmt->execute();
    $staffs = $stmt->fetchAll(PDO::FETCH_ASSOC);
     foreach ($staffs as $staff)
     {
         print_r($staff);
     }
} catch (PDOException $e) {
    echo '错误信息：' . $e;
}