php cheatsheet,24.cheatsheet

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]