開発メモ,主に補足by子連れ親父プログラマー

2012-09-14

mysqli(MySQL 改良版拡張モジュール)を使う場合のサンプルいろいろ

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

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


このブログを検索

Powered by Blogger.

ラベル

php (17) jQuery (13) OSX (10) MySQL (8) Javascript (7) Postgres (7) port (7) apache (6) Java (3) Smarty (2) html (2) pear (2) FCKEditor (1) XAMPP (1) css (1) git (1) perl (1) ruby (1)

Facebookバナー