runoops.com

PHP MySQL Where 子句

WHERE 子句用于过滤记录。


WHERE 子句

WHERE 子句用于提取满足指定标准的的记录。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

如需学习更多关于 SQL 的知识,请访问我们的 SQL 教程

下面的实例将从 "myguests" 表中选取所有 email='john@example.com' 的行:


使用 MySQLi

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 WHERE email = ?";

//创建预处理语句
$stmt  = $conn->prepare($sql);
$email = 'john@example.com';
//绑定参数
$stmt->bind_param("s", $email);

//执行查询
$stmt->execute();

//获取查询结果
$result = $stmt->get_result();

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

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 WHERE email = ?";
$email = 'john@example.com';

$stmt = mysqli_prepare($conn, $sql);

//绑定参数
mysqli_stmt_bind_param($stmt, "s", $email);

//执行查询
mysqli_stmt_execute($stmt);

//获取结果
$result = mysqli_stmt_get_result($stmt);
 
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 表格中:

实例

<?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("SELECT id, firstname, lastname FROM myguests WHERE email = :email"); 
    $stmt->execute([':email' => 'john@example.com']);
 
    // 设置结果集为关联数组
    $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>";
?>

Captcha Code

0 笔记

分享笔记

Inline Feedbacks
View all notes