MySQL Select Data

PHP: Selecting Data from MySQL/MariaDB

Selecting Data from a Database

The SQL SELECT statement is used to retrieve data from one or more database tables.

Basic syntax to select specific columns:

SELECT column1, column2, ...
FROM table_name;

To select all columns from a table, use the asterisk (*) wildcard:

SELECT *
FROM table_name;

You can filter results using the WHERE clause, sort results using ORDER BY, and limit results using LIMIT. For more details on SQL syntax, refer to dedicated SQL tutorials.

Connecting and Selecting Data with PHP

PHP offers two primary extensions for interacting with MySQL/MariaDB databases:

• PDO (PHP Data Objects): Provides a consistent interface for accessing various databases (MySQL, PostgreSQL, SQLite, etc.). Generally recommended for its portability and features.
• MySQLi (MySQL Improved): Offers both procedural and object-oriented interfaces specifically for MySQL/MariaDB. It’s performant and provides access to MySQL-specific features.

Best Practice: Prepared Statements

It is highly recommended to always use prepared statements when executing SQL queries, especially if they involve any user input (even for SELECT queries with WHERE, LIMIT, or OFFSET clauses). Prepared statements prevent SQL injection vulnerabilities and can improve performance for repeated queries.

Example: Selecting Data with PDO (Recommended)

This example connects using PDO, selects data using a prepared statement (even though no parameters are bound in this simple case, it establishes good practice), fetches results, and displays them in an HTML table.

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDBPDO";       // Replace with your DB name

try {
    // Create PDO connection
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);

    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare SQL statement (good practice even without parameters here)
    $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname");

    // Execute the statement
    $stmt->execute();

    // Set the resulting array to associative
    // fetchAll() fetches all results into an array
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (count($result) > 0) {
        echo "<table border='1'><tr><th>ID</th><th>Name</th></tr>";
        // Output data of each row
        foreach($result as $row) {
            echo "<tr><td>" . htmlspecialchars($row["id"]) . "</td><td>" . htmlspecialchars($row["firstname"]) . " " . htmlspecialchars($row["lastname"]) . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "0 results found";
    }

} catch(PDOException $e) {
    // Display error message on failure
    echo "Connection failed: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
?>

Example: Selecting Data with MySQLi (Object-Oriented)

This example performs the same task using the MySQLi extension’s object-oriented style.

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB";         // Replace with your DB name

// Enable error reporting for mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    $conn->set_charset("utf8mb4"); // Set character set

    $sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        echo "<table border='1'><tr><th>ID</th><th>Name</th></tr>";
        // Output data of each row using fetch_assoc()
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>" . htmlspecialchars($row["id"]) . "</td><td>" . htmlspecialchars($row["firstname"]) . " " . htmlspecialchars($row["lastname"]) . "</td></tr>";
        }
        echo "</table>";
        // Free result set
        $result->free();
    } else {
        echo "0 results found";
    }

} catch (mysqli_sql_exception $e) {
    // Display error message on failure
    echo "Database error: " . $e->getMessage();
} finally {
    // Close connection if it was established
    if (isset($conn)) {
        $conn->close();
    }
}
?>

Key Steps in Examples:

• Establish a connection using PDO or MySQLi (with credentials and error handling).
• Prepare (optional but recommended) and execute the SELECT SQL query.
• Fetch the results (e.g., using fetchAll(PDO::FETCH_ASSOC) or looping with fetch_assoc() in MySQLi).
• Check if any rows were returned (count() for PDO fetchAll, num_rows for MySQLi).
• Process and display the data (using htmlspecialchars() to prevent XSS when outputting to HTML).
• Clean up: Free the result set (MySQLi) and close the database connection ($conn = null for PDO, $conn->close() for MySQLi).