PHP | MySQL Select Query

Implementation of the SELECT Query

1. SELECT Query using Procedural Method

In the procedural method, we use the mysqli_connect() function to establish a connection and mysqli_query() to execute the query.

Example:

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

$conn = mysqli_connect($servername, $username, $password, $database);

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

// SQL SELECT query
$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No records found";
}

// Close connection
mysqli_close($conn);
?>

Explanation:

1. The connection is established using mysqli_connect().
    
2. The SELECT query fetches id, name, and email from the users table.
    
3. mysqli_query() executes the query.
    
4. If results are found, they are displayed using mysqli_fetch_assoc().
    
5. Finally, the connection is closed using mysqli_close().

2. SELECT Query using Object-Oriented Method

The Object-Oriented approach uses the mysqli class to interact with the database.

Example:

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

$conn = new mysqli($servername, $username, $password, $database);

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

// SQL SELECT query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No records found";
}

// Close connection
$conn->close();
?>

Explanation:

1. A connection is created using the mysqli class.
    
2. The SELECT query retrieves records from the users table.
    
3. The query is executed using $conn->query($sql).
    
4. Results are processed using $result->fetch_assoc().
    
5. Finally, the connection is closed with $conn->close().

3. SELECT Query using PDO Method

PDO (PHP Data Objects) is a secure and flexible way to interact with databases in PHP.

Example:

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

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

    // SQL SELECT query
    $sql = "SELECT id, name, email FROM users";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Fetch results
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if ($result) {
        foreach ($result as $row) {
            echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
        }
    } else {
        echo "No records found";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Explanation:

1. The connection is created using the PDO class.
    
2. The SELECT query is prepared and executed using $stmt->execute().
    
3. Results are fetched using $stmt->fetchAll(PDO::FETCH_ASSOC).
    
4. Data is displayed in a loop.
    
5. If an error occurs, it is caught using catch (PDOException $e).
    
6. Finally, the connection is closed by setting $conn = null.

Frequently Asked Questions

What is the difference between mysqli and PDO?

mysqli is specific to MySQL databases, while PDO supports multiple databases (MySQL, PostgreSQL, SQLite, etc.). PDO also provides better security with prepared statements.

Which method should I use for database queries in PHP?

For simple applications, mysqli is sufficient. For complex applications requiring flexibility and security, PDO is recommended.

Why should I use prepared statements in database queries?

Prepared statements prevent SQL injection attacks by ensuring user inputs are properly escaped before execution.

Conclusion

In this, we learned on how to use the SELECT query in PHP with MySQL to retrieve data from databases efficiently. We covered different ways to execute queries using MySQLi and PDO, along with best practices for handling results securely. Understanding the SELECT query is essential for managing database-driven applications and ensuring optimal performance in PHP projects.