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();
}

うーん、、こんなとこかー。