php mysql pdo基本操作

[php]代码库

<?php
function db() {
    global $app;
    static $db; //1个请求内多个函数共用1个连接.
    if ($db) {
        return $db;
    } else {
        $db = @new mysqli(
            $app['db_host'], 
            $app['db_user'], 
            $app['db_pass'], 
            $app['db_name'], 
            $app['db_port']
        );
    }
    if ($db->connect_errno) {
        echo $db->connect_error;
        exit();
    }
    $db->set_charset('utf8');
    return $db;
}
 
function insert($title = '', $content = '') {
    global $app;
    $db = db();
    $stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES(?, ?)');
    $stmt->bind_param('ss', $title, $content);
    $stmt->execute();
    return ($stmt->affected_rows !== 0) ? 
        array(true,  'insert_id' => $stmt->insert_id) : 
        array(false, 'insert_id' => $stmt->insert_id);
}
 
function select($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetch_all(MYSQLI_ASSOC);
    } else {
        return $db->query('SELECT * FROM posts')->fetch_all(MYSQLI_ASSOC);
    }
}
 
function select_v2($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        $stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
        $stmt->bind_param('i', $id);
    } else {
        $stmt = $db->prepare('SELECT * FROM posts');
    }
    $stmt->execute();
    //get_result和fetch_all需要mysqlnd支持,PHP从5.4开始内置mysqlnd.
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
 
function update($id, $title = '', $content = '') {
    global $app;
    $db = db();
    $db->autocommit(false);
    //$db->begin_transaction(); //PHP从5.5开始才有这个函数,之前版本用autocommit即可.
    $stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
    $stmt->bind_param('ssi', $title, $content, $id);
    $stmt->execute();
    $db->commit();
    //UPDATE时,如果更新的内容跟原来的内容一样,affected_rows也会返回0.
    return ($stmt->affected_rows !== 0) ? true : false;
}
 
function delete($id) {
    global $app;
    $db = db();
    $db->query('DELETE FROM posts WHERE id = '.intval($id));
    return ($db->affected_rows !== 0) ? true : false;
}
 
function delete_v2($id) {
    global $app;
    $db = db();
    $stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    return ($stmt->affected_rows !== 0) ? true : false;
}
 
header('Content-Type: text/plain; charset=utf-8');
 
$app = array(
    'db_host' => '127.0.0.1',
    'db_user' => 'root',
    'db_pass' => 'xxxx',
    'db_name' => 'test',
    'db_port' => 3306
);
 
$table = "CREATE TABLE IF NOT EXISTS posts (
    id           int(10) unsigned NOT NULL AUTO_INCREMENT,
    post_title   varchar(255)     NOT NULL DEFAULT '',
    post_content text             NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";
 
//db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($table) or exit();
 
echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";
 
echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";
 
echo "var_export(select());\n";
var_export(select());
echo "\n\n";
 
echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";
 
echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";
 
echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";
 
echo "var_export(select());\n";
var_export(select());
echo "\n\n";