PDO
连接数据库
/* 封装配置数据 */
$host = 'localhost';
$dbname = 'bshop';
$username = 'root';
$password = 'yo';
$charset = 'utf8'; // 可选
$collate = 'utf8_unicode_ci'; // 可选
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset"; // 连接语句
$options = [
/* 常用设置 */
PDO::ATTR_CASE => PDO::CASE_NATURAL, /*PDO::CASE_NATURAL | PDO::CASE_LOWER 小写,PDO::CASE_UPPER 大写, */
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, /*是否报错,PDO::ERRMODE_SILENT 只设置错误码,PDO::ERRMODE_WARNING 警告级,如果出错提示警告并继续执行| PDO::ERRMODE_EXCEPTION 异常级,如果出错提示异常并停止执行*/
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL, /* 空值的转换策略 */
PDO::ATTR_STRINGIFY_FETCHES => false, /* 将数字转换为字符串 */
PDO::ATTR_EMULATE_PREPARES => false, /* 模拟语句准备 */
];
/* 连接数据库并实例数据库对象 */
$pdo = new PDO($dsn, $username, $password, $options);
单选
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status=:status LIMIT 1");
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();
多选
$stmt = $pdo->prepare("SELECT * FROM employees WHERE name = :name");
$stmt->execute(['name' => $name]);
foreach ($stmt as $row) {
// do something with $row
}
全选
$news = $pdo->query('SELECT * FROM news')->fetchAll();
插入一条
$row = [
'username' => 'bob',
'email' => 'bob@example.com'
];
$sql = "INSERT INTO users SET username=:username, email=:email;";
$status = $pdo->prepare($sql)->execute($row);
if ($status) {
$lastId = $pdo->lastInsertId();
echo $lastId;
}
插入多条
$rows = [];
$rows[] = [
'username' => 'bob',
'email' => 'bob@example.com'
];
$rows[] = [
'username' => 'max',
'email' => 'max@example.com'
];
$sql = "INSERT INTO users SET username=:username, email=:email;";
$stmt = $pdo->prepare($sql);
foreach ($rows as $row) {
$stmt->execute($row);
}
更新单条
$row = [
'id' => 1,
'username' => 'bob',
'email' => 'bob2@example.com'
];
$sql = "UPDATE users SET username=:username, email=:email WHERE id=:id;";
$status = $pdo->prepare($sql)->execute($row);
更新多条
$row = [
'updated_at' => '2017-01-01 00:00:00'
];
$sql = "UPDATE users SET updated_at=:updated_at";
$pdo->prepare($sql)->execute($row);
$affected = $pdo->rowCount();
删除一行
$where = ['id' => 1];
$pdo->prepare("DELETE FROM users WHERE id=:id")->execute($where);
# 导出数据库
mysqldump -uroot -p bshop > ./xxx/xxx.sql
# 导入数据库
mysql -uroot -p bshop < ./xxx/xxx.sql
.htaccess
Options -MultiViews
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^ index.php [QSA,L]