PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(添削添削)
14993 ワード
PHP MySQLi:http://php.net/mysqli
<?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();
//var_export($db->query('SELECT @@autocommit')->fetch_all(MYSQLI_ASSOC)); exit(); // 1 MySQL .
$db->query('SET AUTOCOMMIT=0'); //$db->autocommit(false);
// ,InnoDB (insert/update/delete) .
// commit autocommit true, delete commit .
$db->query('START TRANSACTION'); //$db->begin_transaction(); PHP 5.5 .
$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
$stmt->bind_param('ssi', $title, $content, $id);
$stmt->execute();
$db->query('COMMIT'); //$db->commit();
//$db->query('ROLLBACK'); //$db->rollback();
$db->query('SET AUTOCOMMIT=1'); //commit autocommit true
//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');
//mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS tuxbase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;GRANT ALL PRIVILEGES ON tuxbase.* TO [email protected] IDENTIFIED BY '913dab0c6788bb8f0';FLUSH PRIVILEGES;"
$app = array(
'db_host' => '127.0.0.1',
'db_user' => 'tux',
'db_pass' => '913dab0c6788bb8f0',
'db_name' => 'tuxbase',
'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'));
";
var_export(insert(' 1', ' 1'));
echo "
";
echo "var_export(insert(' 2', ' 2'));
";
var_export(insert(' 2', ' 2'));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";
echo "var_export(update(2, ' 2_ ',' 2_ '));
";
var_export(update(2, ' 2_ ',' 2_ '));
echo "
";
echo "var_export(select(2));
";
var_export(select(2));
echo "
";
echo "var_export(delete(2));
";
var_export(delete(2));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";<?php
function db() {
global $app;
static $db;
if ($db) {
return $db;
} else {
/* MySQL */
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();
}
/* SQLite
try {
$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');
} 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();
//echo PDO::ATTR_AUTOCOMMIT; // 0 PDO .
//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); // 1 MySQL .
//SQLite PDO::ATTR_AUTOCOMMIT:
//SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'
$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$db->beginTransaction();
$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
$stmt->execute(array($title,$content,$id)); // PDO::PARAM_STR
//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id));
//$stmt->bind_param('ssi', $title, $content, $id); // mysqli
echo 'sleep(3);'."
";
sleep(3);
$db->commit();
$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit autocommit true
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' => 'tux',
'db_pass' => '913dab0c6788bb8f0',
'db_name' => 'tuxbase',
'db_port' => 3306
);
$mysql = "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;";
$sqlite = "CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY,
post_title VARCHAR(255) NOT NULL,
post_content TEXT NOT NULL
)";
db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($mysql) or exit();
// ,SQLite insert
echo "var_export(insert(' 1', ' 1'));
";
var_export(insert(' 1', ' 1'));
echo "
";
echo "var_export(insert(' 2', ' 2'));
";
var_export(insert(' 2', ' 2'));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";
echo "var_export(update(2, ' 2_ ',' 2_ '));
";
var_export(update(2, ' 2_ ',' 2_ '));
echo "
";
echo "var_export(select(2));
";
var_export(select(2));
echo "
";
echo "var_export(delete(2));
";
var_export(delete(2));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";<?php
function db() {
global $app;
static $db;
if ($db) {
return $db;
} else {
/* MySQL
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();
}
*/
/* SQLite */
try {
$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');
} 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();
//echo PDO::ATTR_AUTOCOMMIT; // 0 PDO .
//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); // 1 MySQL .
//SQLite PDO::ATTR_AUTOCOMMIT:
//SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'
//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$db->beginTransaction();
$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
$stmt->execute(array($title,$content,$id)); // PDO::PARAM_STR
//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id));
//$stmt->bind_param('ssi', $title, $content, $id); // mysqli
echo 'sleep(3);'."
";
sleep(3);
$db->commit();
//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit autocommit true
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' => 'tux',
'db_pass' => '913dab0c6788bb8f0',
'db_name' => 'tuxbase',
'db_port' => 3306
);
$mysql = "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;";
$sqlite = "CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY,
post_title VARCHAR(255) NOT NULL,
post_content TEXT NOT NULL
)";
db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($sqlite) or exit();
// ,SQLite insert
echo "var_export(insert(' 1', ' 1'));
";
var_export(insert(' 1', ' 1'));
echo "
";
echo "var_export(insert(' 2', ' 2'));
";
var_export(insert(' 2', ' 2'));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";
echo "var_export(update(2, ' 2_ ',' 2_ '));
";
var_export(update(2, ' 2_ ',' 2_ '));
echo "
";
echo "var_export(select(2));
";
var_export(select(2));
echo "
";
echo "var_export(delete(2));
";
var_export(delete(2));
echo "
";
echo "var_export(select());
";
var_export(select());
echo "
";