一、链接数据库
$config_invite['database'] = array(
'dsn' => 'mysql:dbname=game;host=127.0.0.1;charset=utf8',
'user' => 'root',
'password' => ''
);
try {
$db = new PDO($config_invite['database']['dsn'], $config_invite['database']['user'], $config_invite['database']['password']);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
二、查询之query,会返回一个对象直接打印是不显示的,可以用循环打印出数据
$sql = 'select id,player from Delete_log';
$of = $db->query($sql);
foreach ($of as $k) {
var_dump($k['id']);
var_dump($k['player']);
exit;
}
结果像这样
查询之prepare execute fetchAll 组合用法
prepare:准备要执行的SQL语句并返回一个 PDOStatement 对象
execute:执行一条预处理语句 可以替换变量 参考手册 https://www.runoob.com/php/pdostatement-execute.html
fetchAll:返回一个包含结果集中所有行的数组
fetch:从结果集中获取下一行
$sql = 'select id,player from Delete_log where id=:id';
$sth = $db->prepare($sql);
$sth->execute([':id'=>1]);
$data_list = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($data_list);
三、execute执行limit分页数据的时候会出现bug,可用如下方法解决
$sql = 'select id,player from Delete_log limit :page, :list_rows';
$exe[':page'] = 0;
$exe[':list_rows'] = 10;
$sth = $db->prepare($sql);
foreach ($exe as $key => $value) {
$sth->bindValue($key, $value, select_data_type($value));
}
$sth->execute();
$list = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($list);
//修改数据类型用于execute limit查询bug
function select_data_type($val)
{
if (is_bool($val)) {
return PDO::PARAM_BOOL;
} elseif (is_int($val)) {
return PDO::PARAM_INT;
} elseif (is_null($val)) {
return PDO::PARAM_NULL;
} else {
return PDO::PARAM_STR;
}
}
跟这个一个道理,意思就是好像他会吧分页的变量变成字符串从而导致查询失败,所以强制转成int类型
$sql = 'select id,player from Delete_log limit :page, :list_rows';
$sth = $db->prepare($sql);
$sth->bindValue(':page', (int) 0, PDO::PARAM_INT);
$sth->bindValue(':list_rows', (int) 10, PDO::PARAM_INT);
$sth->execute();
$list = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($list);
这样就可以确保分页查询bug问题