PHP MySQL operations and methods for reading data

PHP MySQL Reading data plays an important role in database operations. This article will explain the operation of reading data in detail.

Read data from MySQL database

The SELECT statement is used to read data from the data table:

SELECT column_name(s) FROM table_name

We can use the * sign to read fields in all data tables:

SELECT * FROM table_name

To learn more about SQL, please visit our SQL Tutorial.

Using MySQLi

In the following example, we read the data of the id, firstname and lastname columns from the MyGuests table of the myDB database and display it on the page:

Example (MySQLi - Object-oriented)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB"; 
// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($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();?>

The above code is analyzed as follows:

First, we set up the SQL statement to read from the MyGuests data table Take three fields: id, firstname and lastname. We then use the modified SQL statement to retrieve the result set from the database and assign it to the copied variable $result.

Function num_rows() determines the returned data.

If multiple pieces of data are returned, the function fetch_assoc() will put the combined set into an associative array and output it in a loop. while() loops out the result set and outputs the three field values ​​id, firstname and lastname.

The following example uses MySQLi's process-oriented approach, and the effect is similar to the above code:

Example (MySQLi - process-oriented)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB"; 
// 创建连接$conn = mysqli_connect($servername, $username, $password, $dbname);// Check connectionif (!$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);?>

Use PDO (preprocessing)

The following examples use prepared statements.

Selected the id, firstname and lastname fields in the MyGuests table and placed them in the HTML table:

Example (PDO)

<?phpecho "<table style=&#39;border: solid 1px black;&#39;>";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=&#39;width:150px;border:1px solid black;&#39;>" . 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"); 
    $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>";?>

This article explains how to read the database Get the operation and related knowledge. For more learning materials, please pay attention to the php Chinese website to view.

Related recommendations:

How to use PHP to send emails

Understanding and use of PHP Cookie related knowledge

PHP Session understanding and application of cache-related knowledge

The above is the detailed content of PHP MySQL operations and methods for reading data. For more information, please follow other related articles on the PHP Chinese website!