您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 入門

PHP MySQL SELECT 查詢完整教學

編輯：PHP 入門

PHP MySQL SELECT 查詢完整教學

基本語法

SELECT column_list FROM table_name [WHERE condition] [ORDER BY ...] [LIMIT ...]

MySQLi 物件導向範例

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'myDB');
if ($mysqli->connect_errno) die('連線失敗: ' . $mysqli->connect_error);

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<br> id: {$row['id']} - Name: {$row['firstname']} {$row['lastname']}";
    }
} else {
    echo "0 個結果";
}
$result->free();
$mysqli->close();
?>

MySQLi 程序式範例

<?php
$conn = mysqli_connect('localhost', 'username', 'password', 'myDB');
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 "<br> id: {$row['id']} - Name: {$row['firstname']} {$row['lastname']}";
    }
} else {
    echo "0 個結果";
}
mysqli_free_result($result);
mysqli_close($conn);
?>

PDO 範例 (推薦)

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=myDB;charset=utf8', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->query("SELECT id, firstname, lastname, email, reg_date FROM MyGuests");
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    echo "<table border='1' cellpadding='5'>
        <tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Reg date</th></tr>";

    foreach ($stmt as $row) {
        echo "<tr>";
        foreach ($row as $val) {
            echo "<td>" . htmlspecialchars($val) . "</td>";
        }
        echo "</tr>";
    }
    echo "</table>";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

進階：自定義迭代器輸出表格 (PDO)

<?php
class TableRows extends RecursiveIteratorIterator {
    public function __construct($it) {
        parent::__construct($it, self::LEAVES_ONLY);
    }
    public function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current() . "</td>";
    }
    public function beginChildren() { echo "<tr>"; }
    public function endChildren() { echo "</tr>\n"; }
}

try {
    $pdo = new PDO('mysql:host=localhost;dbname=myDB;charset=utf8', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo->query("SELECT * FROM MyGuests");
    foreach (new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k => $v) {
        echo $v;
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

常用獲取模式

模式常數	說明
PDO::FETCH_ASSOC	關聯陣列 (欄位名為鍵)
PDO::FETCH_NUM	數字索引陣列
PDO::FETCH_BOTH	同時包含關聯與數字 (預設)
PDO::FETCH_OBJ	匿名物件 (屬性為欄位名)
PDO::FETCH_CLASS	映射到指定類別
PDO::FETCH_LAZY	延遲載入 (結合 FETCH_OBJ/BOTH)
PDO::FETCH_KEY_PAIR	第一欄為鍵、第二欄為值
PDO::FETCH_COLUMN	指定單一欄位 (配合 setFetchMode)