PHP MySQL Using the WHERE Clause

🔍 PHP MySQL – Using the WHERE Clause

The WHERE clause in MySQL allows you to filter records based on a condition. In PHP, you can use it with SELECT, UPDATE, DELETE, and other SQL queries.

1️⃣ Using `mysqli` (Procedural) – SELECT with WHERE

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

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

// Select specific record using WHERE
$sql = "SELECT id, name, email, age FROM users WHERE age > 25";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "ID: ".$row["id"]." - Name: ".$row["name"]." - Age: ".$row["age"]."<br>";
}
} else {
echo "0 results";
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Select specific record using WHERE

$sql = "SELECT id, name, email, age FROM users WHERE age > 25";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

while($row = mysqli_fetch_assoc($result)) {

echo "ID: ".$row["id"]." - Name: ".$row["name"]." - Age: ".$row["age"]."<br>";

}

} else {

echo "0 results";

}

mysqli_close($conn);

✅ Filters all users with age greater than 25.

2️⃣ Using `mysqli` Object-Oriented – SELECT with WHERE

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

$sql = "SELECT * FROM users WHERE name='Vipul'";
$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 "0 results";
}

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT * FROM users WHERE name='Vipul'";

$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 "0 results";

}

$conn->close();

✅ You can use =, >, <, >=, <=, <>, LIKE, BETWEEN, etc. in the WHERE clause.

3️⃣ Using PDO – SELECT with WHERE

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$age = 28;
$stmt = $conn->prepare("SELECT * FROM users WHERE age = :age");
$stmt->bindParam(':age', $age);
$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "ID: ".$user['id']." - Name: ".$user['name']." - Age: ".$user['age']."<br>";
}

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$age = 28;

$stmt = $conn->prepare("SELECT * FROM users WHERE age = :age");

$stmt->bindParam(':age', $age);

$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {

echo "ID: ".$user['id']." - Name: ".$user['name']." - Age: ".$user['age']."<br>";

}

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

✅ PDO prepared statements are secure for user input.

4️⃣ Using WHERE with UPDATE

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

$sql = "UPDATE users SET age=29 WHERE name='Vipul'";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "UPDATE users SET age=29 WHERE name='Vipul'";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . $conn->error;

}

$conn->close();

✅ Only the record matching the WHERE condition is updated.

5️⃣ Using WHERE with DELETE

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

$sql = "DELETE FROM users WHERE age < 25";

if ($conn->query($sql) === TRUE) {
echo "Record(s) deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

$sql = "DELETE FROM users WHERE age < 25";

if ($conn->query($sql) === TRUE) {

echo "Record(s) deleted successfully";

} else {

echo "Error deleting record: " . $conn->error;

}

$conn->close();

✅ Without a WHERE clause, all records will be deleted – be careful!

6️⃣ Key Points

The WHERE clause filters records based on conditions.
Can be used with SELECT, UPDATE, DELETE.
Supports operators: =, >, <, >=, <=, <>, LIKE, BETWEEN, IN, etc.
Always use prepared statements for user-supplied input to prevent SQL injection.
Forgetting WHERE in UPDATE or DELETE can affect all records.

PHP MySQL Using the WHERE Clause