轻松上手,快乐学习!

PHP 教程

PHP 教程PHP 简介PHP 下载安装PHP 语法PHP 注释PHP 变量PHP Echo / PrintPHP 数据类型PHP 字符串PHP 数值PHP MathPHP 常量PHP 运算符PHP If...ElsePHP SwitchPHP 循环While 循环Do While 循环For 循环Foreach 循环Break/ContinuePHP 函数PHP 数组索引数组关联数组多维数组数组排序PHP 全局变量$GLOBALS$_SERVER$_REQUEST$_POST$_GETPHP 正则表达式PHP 表单处理PHP 表单验证PHP 表单验证必填字段PHP 表单验证URL/E-mailPHP 表单验证实例PHP 日期和时间PHP IncludePHP 文件处理PHP 文件打开/读取PHP 文件创建/写入PHP 文件上传PHP CookiesPHP SessionsPHP FiltersPHP Filters AdvancedPHP 回调函数PHP JSONPHP 异常处理PHP 什么是 OOPPHP 类和对象PHP 构造函数PHP 析构函数PHP 访问修饰符PHP 继承PHP 类常量PHP 抽象类PHP 接口PHP 特征PHP 静态方法PHP 静态属性PHP 命名空间PHP 可迭代对象MySQL DatabaseMySQL ConnectMySQL Create DBMySQL Create TableMySQL Insert DataMySQL Get Last IDMySQL Insert MultipleMySQL PreparedMySQL Select DataMySQL WhereMySQL Order ByMySQL Delete DataMySQL Update DataMySQL Limit DataPHP XML ParsersPHP SimpleXML ParserPHP SimpleXML - GetPHP XML ExpatPHP XML DOMAJAX IntroAJAX PHPAJAX DatabaseAJAX XMLAJAX Live SearchAJAX PollPHP 实例PHP 编译器PHP 测验PHP 练习

PHP 参考手册

PHP 概述PHP ArrayPHP CalendarPHP DatePHP DirectoryPHP ErrorPHP ExceptionPHP FilesystemPHP FilterPHP FTPPHP JSONPHP KeywordsPHP LibxmlPHP MailPHP MathPHP MiscPHP MySQLiPHP NetworkPHP Output ControlPHP RegExPHP SimpleXMLPHP StreamPHP StringPHP Variable HandlingPHP XML ParserPHP ZipPHP Timezones


PHP MySQL WHERE 语句


从 MySQL 数据库中选择并筛选数据

WHERE 子句用于筛选记录。

WHERE 子句用于仅提取满足指定条件的记录。

SELECT column_name(s) FROM table_name WHERE column_name operator value 

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


使用 MySQLi 选择并过滤数据

以下示例从 MyGuests 表中选择 id、firstname 和 lastname 列,其中 lastname 为 "Doe",并将其显示在页面上:

实例 (MySQLi - 面向对象)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>
运行实例 »

以上代码解析如下:

首先,我们设置 SQL 查询,从 MyGuests 表中选择 id、firstname 和 lastname 列,其中 lastname 为 "Doe"。下一行代码运行查询并将结果数据放入名为 $result 的变量中。

然后,函数 function num_rows() 检查返回的行数是否超过零。

如果返回的行数超过零,函数 fetch_assoc() 将所有结果放入一个关联数组中,我们可以循环使用该数组。while() 循环遍历结果集,并从 id、firstname 和 lastname 列输出数据。

以下示例以 MySQLi 过程方式实现与上述示例相同的内容:

实例 (MySQLi - 面向过程)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
mysqli_close($conn);
?>
运行实例 »

您还可以将结果放入 HTML 表格中:

实例 (MySQLi - 面向对象)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo "<table><tr><th>ID</th><th>Name</th></tr>";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
  }
  echo "</table>";
} else {
  echo "0 results";
}
$conn->close();
?>
运行实例 »

使用 PDO (+ 预处理)

以下实例使用了预处理语句。

从 MyGuests 表中选择 id、firstname 和 lastname 列,其中 lastname 为 "Doe" ,并将其显示在 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("SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'");
  $stmt->execute();
  // set the resulting array to associative
  $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>";
?>
运行实例 »