Displaying records from a MySQL database using PHP in procedural style involves querying the database, retrieving the records, and then displaying them in a structured format such as an HTML table.

Steps for Displaying Records:

1. Connect to the MySQL database.
2. Write the SQL query to retrieve the records.
3. Execute the query using mysqli_query().
4. Fetch the results using mysqli_fetch_assoc() or mysqli_fetch_array().
5. Display the results using an HTML structure (e.g., a table).
6. Close the database connection.

Example: Displaying Records from a MySQL Table

Assume you have a table called users with the following fields: id, username, email, and created_at.

Step 1: Connect to the Database and Execute the Query

<?php
// Step 1: Connect to the MySQL database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create the connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check if the connection is successful
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Step 2: Write the SQL query to retrieve records
$sql = "SELECT id, username, email, created_at FROM users";

// Step 3: Execute the query
$result = mysqli_query($conn, $sql);

// Step 4: Check if records were returned
if (mysqli_num_rows($result) > 0) {
    // Step 5: Display records in an HTML table
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Username</th>
                <th>Email</th>
                <th>Created At</th>
            </tr>";
    
    // Step 6: Loop through and fetch the records
    while ($row = mysqli_fetch_assoc($result)) {
        echo "<tr>
                <td>" . $row['id'] . "</td>
                <td>" . $row['username'] . "</td>
                <td>" . $row['email'] . "</td>
                <td>" . $row['created_at'] . "</td>
              </tr>";
    }
    
    echo "</table>";
} else {
    echo "No records found.";
}

// Step 7: Close the database connection
mysqli_close($conn);
?>

Explanation:

Step 1: Connect to the Database

• We use mysqli_connect() to establish a connection to the MySQL database using the database credentials (server name, username, password, and database name).

Step 2: Write the SQL Query

• The SQL query retrieves the id, username, email, and created_at columns from the users table.

Step 3: Execute the Query

• The mysqli_query() function is used to execute the query and return the result set.

Step 4: Check for Records

• mysqli_num_rows($result) checks if there are any records in the result set. If there are, it proceeds to display them.

Step 5: Display Records in an HTML Table

• We start by creating an HTML table with headers for the ID, Username, Email, and Created At columns.

Step 6: Fetch and Display Records

• The while loop iterates through each row in the result set, and the mysqli_fetch_assoc() function fetches the data as an associative array.
• We then echo each row inside the table.

Step 7: Close the Database Connection

• After processing the records, the connection to the database is closed using mysqli_close() to free up resources.

Example Output:

+----+----------+-------------------+---------------------+
| ID | Username | Email             | Created At          |
+----+----------+-------------------+---------------------+
| 1  | John     | john@example.com   | 2024-09-01 14:22:00 |
| 2  | Jane     | jane@example.com   | 2024-09-02 10:15:00 |
+----+----------+-------------------+---------------------+

Fetching Data Using Different Methods

You can fetch data in different formats depending on your needs:

1. mysqli_fetch_assoc(): Returns the data as an associative array (column names as keys).

   • Example: ['id' => 1, 'username' => 'John']

2. mysqli_fetch_array(): Returns the data as both an associative array and a numeric array.

   • Example: ['id' => 1, 'username' => 'John', 0 => 1, 1 => 'John']

3. mysqli_fetch_row(): Returns the data as a numeric array (no column names).

   • Example: [1, 'John']

Example using mysqli_fetch_array():

while ($row = mysqli_fetch_array($result)) {
    echo "<tr>
            <td>" . $row['id'] . "</td>
            <td>" . $row['username'] . "</td>
            <td>" . $row['email'] . "</td>
            <td>" . $row['created_at'] . "</td>
          </tr>";
}

Pagination (Optional)

For large datasets, it's common to implement pagination to display a limited number of records per page. This is done using the LIMIT clause in SQL and adjusting the query based on the page number.

// Assuming $page contains the current page number
$limit = 10;  // Number of records per page
$offset = ($page - 1) * $limit;  // Calculate the offset
$sql = "SELECT id, username, email, created_at FROM users LIMIT $limit OFFSET $offset";

Conclusion

To display records from a MySQL database using PHP in procedural style:

1. Connect to the database using mysqli_connect().
2. Write and execute the SQL query using mysqli_query().
3. Fetch the records using functions like mysqli_fetch_assoc() or mysqli_fetch_array().
4. Display the data in a structured format, such as an HTML table.
5. Always close the connection using mysqli_close().