How can we display all the records from MySQL table with the help of PHP script?

To display all records from a MySQL table using PHP, you need to establish a database connection, execute a SELECT query, and fetch the results. Below is an example using modern PHP and MySQLi extension ?

Using MySQLi Extension

The following example demonstrates fetching all records from a table named tutorials_tbl ?

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

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

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
            FROM tutorials_tbl";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            echo "Tutorial ID: " . $row["tutorial_id"] . "<br>";
            echo "Title: " . $row["tutorial_title"] . "<br>";
            echo "Author: " . $row["tutorial_author"] . "<br>";
            echo "Submission Date: " . $row["submission_date"] . "<br>";
            echo "--------------------------------<br>";
        }
    } else {
        echo "0 results found";
    }
    
    $conn->close();
?>

Using PDO Extension

Alternatively, you can use PDO (PHP Data Objects) for better security and flexibility ?

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

    try {
        $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
                FROM tutorials_tbl";
        $stmt = $pdo->query($sql);

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo "Tutorial ID: " . $row["tutorial_id"] . "<br>";
            echo "Title: " . $row["tutorial_title"] . "<br>";
            echo "Author: " . $row["tutorial_author"] . "<br>";
            echo "Submission Date: " . $row["submission_date"] . "<br>";
            echo "--------------------------------<br>";
        }
    } catch(PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    
    $pdo = null;
?>

Key Points

When fetching data from MySQL with PHP, remember these important points ?

• fetch_assoc() returns an associative array where column names are keys
• num_rows property checks if any records were found
• Always close database connections to free up resources
• Use prepared statements for queries with user input to prevent SQL injection

Conclusion

Both MySQLi and PDO extensions provide modern ways to fetch MySQL records in PHP. PDO offers better portability across different database systems, while MySQLi is specifically optimized for MySQL databases.