How to Query MySQL with PHP and Return Results as an Array

Before starting, we need to prepare the following:

A web server with PHP and MySQL installed

A MySQL database containing some data

Knowledge of how to connect to the MySQL database

Assuming these are ready, we can start querying MySQL with PHP and returning the results as an array.

First, we create a PHP script that connects to the MySQL database using the mysqli class. Below is an example of establishing the connection:

<?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("连接失败: " . $conn->connect_error);
}
?>

We use four variables to represent the server name, username, password, and database name, create a new mysqli object, and verify that the connection succeeded. If the connection fails, an error message is displayed and the script exits.

Next, we use a SELECT statement to retrieve data from the MySQL database. The following example selects all rows from a table named customers:

<?php
$sql = "SELECT * FROM customers";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Store query results in an array
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
} else {
    echo "0 结果";
}
?>

The query() method executes the SELECT statement and checks whether the result set contains rows. If rows are returned, each row is fetched with fetch_assoc() and appended to the $data array.

To display the query results, we iterate over the $data array with a foreach loop and output each field inside an HTML table:

<table>
    <tr>
        <th>姓名</th>
        <th>地址</th>
        <th>城市</th>
        <th>电话号码</th>
    </tr>
    <?php foreach ($data as $row): ?>
        <tr>
            <td><?php echo $row['name']; ?></td>
            <td><?php echo $row['address']; ?></td>
            <td><?php echo $row['city']; ?></td>
            <td><?php echo $row['phone']; ?></td>
        </tr>
    <?php endforeach; ?>
</table>

By querying MySQL with PHP, storing the results in an array, and rendering them in an HTML table, we create a powerful and maintainable way to display database data in a web application.