星期六, 4月 17, 2010

PEAR:DB之2

ABOUT 5. 實際連結MYSQL

在第一篇說了這麼多,實際連結資料庫並在成功連結後印出一段字串。


<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連結成功');

$db->disconnect();

?>

</body>
</html>

ABOUT 6. 執行查詢字串
之前說了這麼多,最重要的目的就是要做到資料庫的查詢。而在PRAE:DB執行查詢的
方法是query()。

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}
當然在查詢句中我們也可以使用"?"樣的萬用字元,這一個方法有一個好處,就是查詢範
圍可以由瀏覽者自訂。
$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih where id > ? and id < ? ';
$data = array(1, 4);

$res =& $db->query($sql, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}
在第6結的結束之前我們用一個例子還說明查詢並列印的一個完整流程。

<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連結成功');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    print($row['id']);
    print($row['name'].'<br>');
}

$db->disconnect();

?>

</body>
</html>
ABOUT 7. 取得查詢過的結果
送出查詢的SQL後,接下來我們需要用一個變數儲存query傳回的結果,進而在網頁中
表現出其輸出結果。輸出結果用fetchrow()方法。

fetchRow
mixed &fetchRow ([integer $fetchmode = DB_DEFAULT_MODE
[, integer $rownum = NULL]])
FETCHMODE:
DB_FETCHMODE_ORDERED
DB_FETCHMODE_ASSOC
DB_FETCHMODE_OBJECT
下面我們用一個例子來做說明,相信您一定會更了解

<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連結成功');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih where id > ? and id < ?';
$data = array(1, 4);
$res =& $db->query($sql, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

$db->disconnect();

?>

</body>
</html>
需要注意的是FETCHMODE是可以變更的,當然設成不一樣的MODE,當然取值運算子也不同。
ABOUT 8. 其他SQL語法(INSERT、UPDATE、DELETE)
以下用三個範例說明:
1. INSERT
<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連接成功<br>');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

print('<br>追加資料。<br><br>');

$sql = "insert into shouhin (id, name) VALUES (3, '我愛小姐')";
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

print('<br>更新後之資料取得。<br><br>');

$sql = 'select * from shouhin';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

$db->disconnect();

?>

</body>
</html>
2. UPDATE

<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testuser@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連接成功<br>');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

print('<br>更新資料。<br><br>');

$id = 6;
$name = '我愛妹妹';

$sql = "update shih set name = ? WHERE id = ?";
$data = array($name, $id);

$res =& $db->query($sql, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

print('<br>取得更新後資料。<br><br>');

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

$db->disconnect();

?>

</body>
</html>
3. DELETE

<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連結成功<br>');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

print('<br>刪除資料。<br><br>');

$id = 6;

$sql = "delete from shih where id = ?";
$data = $id;

$res =& $db->query($sql, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

print('<br>取得刪除後的資料。<br><br>');

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

$db->disconnect();

?>

</body>
</html>
ABOUT 9. 準備機能(PREPARE、EXECUTE)
用法請看範例
<html>
<head><title>PHP TEST</title></head>
<body>

<?php

require_once 'DB.php';

$dsn = 'mysqli://testuser:testpass@localhost/shih';

$db = DB::connect($dsn);
if (PEAR::isError($db)) {
    die($db->getMessage());
}

print('連結成功<br>');

$db->query('SET NAMES utf8');
if (PEAR::isError($db)) {
    die($db->getMessage());
}

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

print('<br>追加資料。<br><br>');

$sql = "insert into shih (id, name) VALUES (?, ?)";
$stmt = $db->prepare($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

$data = array(6, '水果');
$db->execute($stmt, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

$data = array(7, '果汁');
$db->execute($stmt, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

$data = array(8, '飲料');
$db->execute($stmt, $data);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

print('<br>追加後資料取得。<br><br>');

$sql = 'select * from shih';
$res =& $db->query($sql);
if (PEAR::isError($res)) {
    die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_OBJECT)){
    print($row->id);
    print($row->name.'<br>');
}

$db->disconnect();

?>

</body>
</html>
                                                                             
update : 2010/3/23

沒有留言:

張貼留言