Retrieving data from a MySQL database is a fundamental operation in PHP web development. This guide will walk you through the process of selecting data using PHP and MySQL.

Basic SELECT Statement

The SQL SELECT statement is used to fetch data from a database. Here's a simple example:


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

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "";
    }
} else {
    echo "0 results";
}

This code selects all records from the 'users' table and displays the ID, first name, and last name of each user.

Fetching Results

PHP offers several methods to fetch results:

fetch_assoc(): Returns an associative array
fetch_array(): Returns an array with both associative and numeric keys
fetch_object(): Returns an object

Using WHERE Clause

To filter results, use the WHERE clause in your SQL statement:


$sql = "SELECT * FROM users WHERE age > 18";
$result = $conn->query($sql);

Prepared Statements

For improved security, especially when dealing with user input, use PHP Prepared Statements:


$stmt = $conn->prepare("SELECT * FROM users WHERE lastname = ?");
$stmt->bind_param("s", $lastname);
$stmt->execute();
$result = $stmt->get_result();

Best Practices

Always sanitize user input to prevent SQL injection attacks
Use prepared statements for queries with variables
Close the database connection when finished
Use appropriate indexing on your database tables for faster queries

Error Handling

Implement error handling to manage database connection issues or query failures:


if (!$result) {
    die("Query failed: " . $conn->error);
}

Optimizing Queries

For large datasets, consider using LIMIT to restrict the number of results:


$sql = "SELECT * FROM users LIMIT 10";

This approach is particularly useful for pagination. For more advanced query optimization techniques, refer to our guide on PHP Performance Optimization.

Conclusion

Selecting data from MySQL databases is a crucial skill for PHP developers. By mastering these techniques, you'll be able to efficiently retrieve and manipulate data in your web applications. Remember to always prioritize security and performance when working with databases.