I’m building a simple PHP page that connects to a MySQL database and displays search results from a teacher table. The search checks if the input appears in the name, email, or subject columns. Here’s my current code:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
if ($search === '') {
$sql = "SELECT * FROM teacher";
} else {
$like = "%" . $mysqli->real_escape_string($search) . "%";
$sql = "SELECT * FROM teacher WHERE name LIKE '$like' OR email LIKE '$like' OR subject LIKE '$like'";
}
$result = $mysqli->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Search Results</title>
</head>
<body>
<?php
if ($result->num_rows > 0) {
echo "<h1>Search Results</h1>";
echo "<table>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Subject</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["teacher_id"] . "</td>";
echo "<td>" . $row["name"] . "</td>";
echo "<td>" . $row["email"] . "</td>";
echo "<td>" . $row["subject"] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<h1>No results found</h1>";
}
$mysqli->close();
?>
<a href="xxx_searchform.html">back</a>
</body>
</html>
The code works fine, but I have a few questions:
Is this query safe from SQL injection, or should I use prepared statements even though I’m using real_escape_string?
Is there a better way to structure the SQL logic when the search input is empty versus when it’s not?
Are there any performance or security issues I should be aware of when searching with LIKE across multiple fields like this?
Any improvement suggestions or best practices are appreciated. Thanks!