在第一篇說了這麼多,實際連結資料庫並在成功連結後印出一段字串。
<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
沒有留言:
張貼留言