PHPMySQL讀取數據

從MySQL 資料庫讀取數據

SELECT 語句用於從資料表中讀取資料:

 SELECT column_name(s) FROM table_name

我們可以使用* 號來讀取所有資料表中的欄位：

 SELECT * FROM table_name

如需學習更多關於SQL 的知識，請造訪我們的SQL 教學。

使用MySQLi

以下實例中我們從myDB 資料庫的MyGuests 表讀取了id, firstname 和lastname 欄位的資料並顯示在頁面上：

實例(MySQLi - 物件導向)

<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; //建立連接$conn = new mysqli ( $servername , $username , $password , $dbname ) ; // Check connection if ( $conn -> connect_error ) { die ( "連線失敗: " . $conn -> connect_error ) ; } $sql = " SELECT id, firstname, lastname FROM MyGuests " ; $result = $conn -> query ( $sql ) ; if ( $result -> num_rows > 0 ) { //輸出資料 while ( $row = $result -> fetch_assoc ( ) ) { echo " id: " . $row [ " id " ] . " - Name: " . $row [ " firstname " ] . " " . $row [ " lastname " ] . " <br> " ; } } else { echo " 0 結果" ; } $conn -> close ( ) ; ?>

以上程式碼解析如下:

首先，我們設定了SQL 語句從MyGuests資料表中讀取id, firstname 和lastname 三個欄位。之後我們使用改SQL 語句從資料庫中取出結果集並賦給複製給變數$result。

函數num_rows() 判斷傳回的資料。

如果傳回的是多條數據，函數fetch_assoc() 將結合集放入到關聯數組並循環輸出。 while() 循環出結果集，並輸出id, firstname 和lastname 三個欄位值。

以下實例使用MySQLi 面向過程的方式，效果類似以上程式碼:

實例(MySQLi - 面向過程)

<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; //建立連接$conn = mysqli_connect ( $servername , $username , $password , $dbname ) ; // Check connection if ( ! $conn ) { die ( "連線失敗: " . mysqli_connect_error ( ) ) ; } $sql = " SELECT id, firstname, lastname FROM MyGuests " ; $result = mysqli_query ( $conn , $sql ) ; if ( mysqli_num_rows ( $result ) > 0 ) { //輸出資料 while ( $row = mysqli_fetch_assoc ( $result ) ) { echo " id: " . $row [ " id " ] . " - Name: " . $row [ " firstname " ] . " " . $row [ " lastname " ] . " <br> " ; } } else { echo " 0 結果" ; } mysqli_close ( $conn ) ; ?>

使用PDO (+ 預處理)

以下實例使用了預處理語句。

選取了MyGuests 表中的id, firstname 和lastname 字段，並放到HTML 表格中：

實例(PDO)

<?php echo " <table style='border: solid 1px black;'> " ; echo " <tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr> " ; class TableRows extends RecursiveIteratorIterator { function __construct ( $it ) { parent :: __construct ( $it , self :: LEAVES_ONLY ) ; } function current ( ) { return " <td style='width:150px;border:1px solid black;'> " . parent :: current ( ) . " </td> " ; } function beginChildren ( ) { echo " <tr> " ; } function endChildren ( ) { echo " </tr> " . " n " ; } } $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDBPDO " ; try { $conn = new PDO ( " mysql:host= $servername ;dbname= $dbname " , $username , $password ) ; $conn -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION ) ; $stmt = $conn -> prepare ( "conn -> prepare ( " SELECT id, firstname, lastname FROM MyGuests " ) ; $stmt -> execute ( ) ; //設定結果集為關聯數組 $result = $stmt -> setFetchMode ( PDO :: FETCH_ASSOC ) ; foreach ( new TableRows ( new RecursiveArrayIterator ( $stmt -> fetchAll ( ) ) ) as $k => $v ) { echo $v ; } } catch ( PDOException $e ) { echo " Error: " . $e -> getMessage ( ) ; } $conn = null ; echo " </table> " ; ?>