pdo_msyql基本操作

[php]代码库

<?php
function db() {
    global $app;
    static $db;
    if ($db) {
        return $db;
    } else {
        try {
            $dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";
            $db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(
                PDO::ATTR_PERSISTENT => false,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
            ));
        } catch (PDOException $e) {
            echo $e->getMessage();
            exit();
        }
    }
    return $db;
}
 
function insert($title = '', $content = '') {
    global $app;
    $db = db();
    $stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES(?, ?)');
    $stmt->bindParam(1, $title,   PDO::PARAM_STR);
    $stmt->bindParam(2, $content, PDO::PARAM_STR);
    $stmt->execute();
    return ($stmt->rowCount() !== 0) ? 
        array(true,  'lastInsertId' => $db->lastInsertId()) : 
        array(false, 'lastInsertId' => $db->lastInsertId());
}
 
function select($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);
    } else {
        return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);
    }
}
 
function select_v2($id = '') {
    global $app;
    $db = db();
    if (!empty($id)) {
        $stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
    } else {
        $stmt = $db->prepare('SELECT * FROM posts');
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
 
function update($id, $title = '', $content = '') {
    global $app;
    $db = db();
    $db->setAttribute(PDO::ATTR_AUTOCOMMIT, false); 
    $db->beginTransaction();
    $stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
    $stmt->bindParam(1, $title,   PDO::PARAM_STR);
    $stmt->bindParam(2, $content, PDO::PARAM_STR);
    $stmt->bindParam(3, $id,      PDO::PARAM_INT);
    $stmt->execute();
    $db->commit();
    return ($stmt->rowCount() !== 0) ? true : false;
}
 
function delete($id) {
    global $app;
    $db = db();
    return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false;
}
 
function delete_v2($id) {
    global $app;
    $db = db();
    $stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
    $stmt->bindParam(1, $id, PDO::PARAM_INT);
    $stmt->execute();
    return ($stmt->rowCount() !== 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_v2());
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_v2(2));
echo "\n\n";
 
echo "var_export(delete(2));\n";
var_export(delete_v2(2));
echo "\n\n";
 
echo "var_export(select());\n";
var_export(select_v2());
echo "\n\n";