PHP : MySQL Use The WHERE Clause

To retrieve specific data from a MySQL database table based on certain conditions, you can use the WHERE clause in your SQL SELECT queries. This allows you to filter the results to only include rows that meet the specified conditions. Below are examples using both mysqli and PDO to select data from a users table based on a condition using the WHERE clause.

Using mysqli:

Here's an example of using mysqli to select rows from the users table where the id is equal to 1:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// ID to search for
$id = 1;

// SQL query with WHERE clause
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id); // "i" for integer
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

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

Using PDO :

Here's the same example using PDO :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

    // ID to search for
    $id = 1;

    // SQL query with WHERE clause
    $sql = "SELECT * FROM users WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();

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

    if (count($result) > 0) {
        foreach ($result as $row) {
            echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
        }
    } else {
        echo "0 results";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an SQL SELECT * FROM users WHERE id = ? query to retrieve rows from the users table where the id matches the specified value (1 in this case).
• The WHERE clause is used to filter the results based on the condition.
• In the mysqli example, bind_param("i", $id) is used to bind the parameter $id as an integer.
• In the PDO example, bindParam( :id , $id, PDO::PARAM_INT) is used to bind the parameter $id as an integer.

Important Notes:

• Adjust the $id variable to the value you want to search for.
• The WHERE clause can include multiple conditions joined by AND or OR.
• Always verify that the connection is successful before executing queries.
• Close the connection after use to free up resources.

Running these scripts will select and output rows from the users table where the id matches the specified value ( 1 ). Adjust the condition in the WHERE clause as needed for your specific query.