How to select data from MySQL table using PHP?

How to use PHP to select data from a MySQL table to establish a database connection, using MySQLi or PDO extensions. Prepare query statements. Execute the query and get the results. Use a loop to iterate through the result data and output it.

How to select data from a MySQL table using PHP

Preface

In In PHP, we can select data from MySQL database using MySQLi or PDO extension. This article demonstrates how to achieve this using both extensions.

Using MySQLi extension

1. Establish database connection

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

2. Prepare query

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // 输出数据
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
  }
} else {
  echo "0 结果";
}

$conn->close();

Use PDO extension

1. Establish database connection

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
  die("连接失败: " . $e->getMessage());
}

2. Prepare query

$sql = "SELECT * FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll();

foreach($result as $row) {
  echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

$conn = null;

Practical case

Suppose we have a MySQL table named "users", which stores user data. To select all data from this table, we can use приведенный above code as shown below:

// 使用 MySQLi 扩展
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // 输出数据
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
  }
} else {
  echo "0 结果";
}

$conn->close();

Similarly, we can use PDO extension to select data as shown below:

// 使用 PDO 扩展
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
  die("连接失败: " . $e->getMessage());
}

$sql = "SELECT * FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll();

foreach($result as $row) {
  echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

$conn = null;

The above is the detailed content of How to select data from MySQL table using PHP?. For more information, please follow other related articles on the PHP Chinese website!