mysqli(MySQL 改良版拡張モジュール)を使う場合のサンプルいろいろ
いやあよもやね、mysql_real_escape_string とかが deprecated になる日がくるとは思わんかったね。
まずいでしょう、これは。まじで。
error_reporting(0);
にしとかないとあっという間にサーバーのログがパンクするんじゃないか・・・。
ま、それはともかく、mysqli に書き換えないといけない。
いや、そもそも mysqli でいいのか？ PDO っつーのもあるぞ、って話なんだが。
俺は mysqli でいく。
まずは接続。
// データベース接続
$dbh = new mysqli("localhost", "username", "password", "db_name");
if ($dbh->connect_error) {
die('Db Connect Error: '.$dbh->connect_errno.':'.$dbh->connect_error);
}
$dbh->set_charset('utf8');
id= で一個だけのデータをセレクトする。
$sth = $dbh->stmt_init();
$sql = "SELECT id, name FROM users WHERE id = ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('s', $id);
$id = '1';
$sth->execute();
$result = $sth->get_result();
while ($row = $result->fetch_assoc()) {
echo $row['id']."\n";
echo $row['name']."\n";
}
$sth->close();
}
同じsqlをセレクトする値を変えて複数回実行する。
$sth = $dbh->stmt_init();
$sql = "SELECT id, name FROM users WHERE id = ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('s', $id);
$id_array = array('1', '2');
foreach($id_array as $id) {
$sth->execute();
$result = $sth->get_result();
while ($row = $result->fetch_assoc()) {
foreach($row as $k => $v) {
echo "$k $v";
}
}
}
$sth->close();
}
クエリの結果の行数を取得してなんかする。
$sth = $dbh->stmt_init();
$sql = "SELECT id, name FROM users";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->execute();
$sth->store_result();
if ($sth->num_rows == '3') {
$sth->bind_result($id, $name);
while ($sth->fetch()) {
echo "$id, $name\n";
}
printf("Number of rows: %d.\n", $sth->num_rows);
}
$sth->free_result();
$sth->close();
}
クエリでINを使う。
$sth = $dbh->stmt_init();
$sql = "SELECT id, name FROM users WHERE id IN (?, ?)";
if (! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$list = array('1','3');
$sth->bind_param('ii', $list[0], $list[1]);
$sth->execute();
$sth->store_result();
$sth->bind_result($id, $name);
while ($sth->fetch()) {
echo "$id, $name\n";
}
$sth->free_result();
$sth->close();
}
使えねーなこりゃ。
次、LIKE検索。
$sth = $dbh->stmt_init();
$sql = "SELECT id, name FROM users WHERE name LIKE ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('s', $param);
$myname = "理";
$param = "%".$myname."%";
$sth->execute();
$sth->store_result();
$sth->bind_result($id, $name);
while ($sth->fetch()) {
echo "$id, $name\n";
}
$sth->free_result();
$sth->close();
}
一行だけ更新。
$sth = $dbh->stmt_init();
$sql = "UPDATE users SET name = ? WHERE id = ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('ss', $myname, $myid);
$myname = "里香";
$myid = "3";
$sth->execute();
printf("%d Row affected.\n", $sth->affected_rows);
$sth->close();
}
ループで複数行更新。
$sth = $dbh->stmt_init();
$sql = "UPDATE users SET name = ? WHERE id = ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('ss', $myname, $myid);
$id_array = array('8'=>'佐藤', '2'=>'山田');
foreach($id_array as $myid => $myname) {
$sth->execute();
}
printf("%d Row affected.\n", $sth->affected_rows);
$sth->close();
}
削除。
$sth = $dbh->stmt_init();
$sql = "DELETE FROM users WHERE id = ?";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('s', $id);
$id = "2";
$sth->execute();
printf("%d Row affected.\n", $sth->affected_rows);
$sth->close();
}
インサートとインサートしたIDの取得。
$sth = $dbh->stmt_init();
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('ss', $myname, $myemail);
$myname = "歌代";
$myemail = "xxxx@xxxx.xxx";
$sth->execute();
// INSERTED_ID の取得
printf("%d Inserted.\n", $dbh->insert_id);// こっち
printf("%d Inserted.\n", $sth->insert_id);
$sth->close();
}
インサート処理でエラー処理を付ける。
$sth = $dbh->stmt_init();
$sql = "INSERT INTO users (id, name, email) VALUES (?, ?, ?)";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('iss', $myid, $myname, $myemail);
$myid = "2";
$myname = "歌代";
$myemail = "xxxx@xxxx.xxx";
if ( ! $sth->execute()) {
die('failed'.$sth->error);
}
$sth->close();
}
トランザクションで失敗したらロールバック。
$dbh->autocommit(FALSE);
$sth = $dbh->stmt_init();
$sql = "INSERT INTO myusers (name, email) VALUES (?, ?)";
if ( ! $sth->prepare($sql)) {
die('Failed to prepare statement\n');
} else {
$sth->bind_param('ss', $myname, $myemail);
$myname = "夢";
$myemail = "xxxx@xxxx.xxx";
if ( ! $sth->execute()) {
$dbh->rollback();
} else {
$dbh->commit();
}
$sth->close();
}
うーん、、こんなとこかー。
