In MySQLi, the WHERE clause is used to filter records based on specified conditions. You can use it in both SELECT, UPDATE, DELETE, or any other query that needs to filter results.

Example: WHERE Clause in MySQLi (Object-Oriented)

Select Data Using WHERE

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Your database name// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// SQL query with WHERE clause to select data$sql = "SELECT id, name, email FROM users WHERE id = ?"; // Filter by 'id'// Prepare the statement$stmt = $conn->prepare($sql);// Bind the parameter$stmt->bind_param("i", $id); // 'i' is for integer type// Set the value for the 'id' parameter$id = 1; // Get user with ID 1// Execute the query$stmt->execute();// Store the result$stmt->store_result();// Bind result variables$stmt->bind_result($user_id, $user_name, $user_email);// Fetch and display the resultif ($stmt->fetch()) {    echo "ID: " . $user_id . " - Name: " . $user_name . " - Email: " . $user_email;} else {    echo "No record found with ID: " . $id;}// Close the statement and connection$stmt->close();$conn->close();?>

Explanation:

• WHERE id = ?: This query filters the users table by the id column.

• $stmt->bind_param("i", $id): Binds the parameter to the prepared statement (i is for integer). We are passing the value for id dynamically.

• $stmt->bind_result($user_id, $user_name, $user_email): Binds the result columns to PHP variables.

• $stmt->fetch(): Fetches the result and stores it in the variables.

Example: WHERE Clause in MySQLi (Procedural)

Select Data Using WHERE

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Your database name// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query with WHERE clause to select data$sql = "SELECT id, name, email FROM users WHERE id = ?";// Prepare the statement$stmt = mysqli_prepare($conn, $sql);// Bind the parametermysqli_stmt_bind_param($stmt, "i", $id); // 'i' is for integer type// Set the value for the 'id' parameter$id = 1; // Get user with ID 1// Execute the querymysqli_stmt_execute($stmt);// Bind the result variablesmysqli_stmt_bind_result($stmt, $user_id, $user_name, $user_email);// Fetch and display the resultif (mysqli_stmt_fetch($stmt)) {    echo "ID: " . $user_id . " - Name: " . $user_name . " - Email: " . $user_email;} else {    echo "No record found with ID: " . $id;}// Close the statement and connectionmysqli_stmt_close($stmt);mysqli_close($conn);?>

Explanation:

• WHERE id = ?: The query filters the results based on the id column.

• mysqli_stmt_bind_param($stmt, "i", $id): Binds the $id parameter to the prepared statement.

• mysqli_stmt_bind_result($stmt, $user_id, $user_name, $user_email): Binds the result columns to PHP variables.

• mysqli_stmt_fetch($stmt): Fetches the result and populates the bound variables.

Using WHERE with Multiple Conditions:

You can also use multiple conditions in the WHERE clause. For example, to filter records based on both name and email, you can do:

$sql = "SELECT id, name, email FROM users WHERE name = ? AND email = ?";

And then bind both parameters:

$stmt->bind_param("ss", $name, $email); // 's' is for string type

WHERE with IN Clause Example:

You can also use the IN operator to filter records by a list of values.

$sql = "SELECT id, name, email FROM users WHERE id IN (?, ?, ?)";

And bind the parameters:

$stmt->bind_param("iii", $id1, $id2, $id3); // Multiple integer parameters

Key Points:

1. Prepared Statements: Using prepared statements with WHERE clauses ensures security and helps prevent SQL injection.

2. Binding Parameters: Use bind_param() for binding variables to the query and passing values safely.

3. Fetching Data: Use fetch() or bind_result() to retrieve and display the result.

This should help you use the WHERE clause effectively with MySQLi. Let me know if you have more questions!