Keywords: PHP | MySQL | Search Form | Database Query | SQL Injection Prevention
Abstract: This article provides a comprehensive exploration of implementing database search forms with PHP and MySQL. It systematically covers database connection, form design, query processing, and result display, addressing common errors and their solutions. Through refactored code examples, it emphasizes critical issues like case sensitivity in table names and SQL injection prevention, offering a complete, reusable code framework.
Introduction
Database search functionality is a core requirement in modern web development. Based on a practical case of a packaging database search, this article delves into the technical details of implementing search forms using PHP and MySQL. We start from database creation and progressively build a complete search system, addressing common errors encountered during development.
Database Design and Creation
First, it is essential to correctly create the database and table structure. In PHP, use the mysql_connect() function to establish a database connection, then execute SQL statements via mysql_query() to create the database and tables. Note that MySQL table names are case-sensitive in most systems, meaning Liam and liam are treated as distinct tables.
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
if (mysql_query("CREATE DATABASE db_test", $con)) {
echo "Database created";
} else {
echo "Error creating database: " . mysql_error();
}
mysql_select_db("db_test", $con);
$sql = "CREATE TABLE Liam (
Code varchar(30),
Description varchar(30),
Category varchar(30),
CutSize varchar(30)
)";
mysql_query($sql, $con);
mysql_close($con);
?>Search Form Design
The HTML form serves as the interface for user interaction with the search system. A basic search form includes a text input field and a submit button, using the POST method to pass the user's search term to the processing script.
<form action="search.php" method="post">
Search: <input type="text" name="term" /><br />
<input type="submit" value="Submit" />
</form>PHP Search Processing Logic
In the PHP processing script, securely retrieve user input and construct the SQL query. Using the mysql_real_escape_string() function to escape user input effectively prevents SQL injection attacks.
<?php
$db_hostname = 'localhost';
$db_username = 'demo';
$db_password = 'demo';
$db_database = 'demo';
$con = mysql_connect($db_hostname, $db_username, $db_password);
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_database, $con);
if (!empty($_REQUEST['term'])) {
$term = mysql_real_escape_string($_REQUEST['term']);
$sql = "SELECT * FROM Liam WHERE Description LIKE '%".$term."%'";
$r_query = mysql_query($sql);
while ($row = mysql_fetch_array($r_query)) {
echo 'Code: ' . $row['Code'];
echo '<br /> Description: ' . $row['Description'];
echo '<br /> Category: ' . $row['Category'];
echo '<br /> Cut Size: ' . $row['CutSize'];
echo '<br /><br />';
}
}
?>Common Error Analysis and Solutions
Common errors during development include inconsistent table name casing, SQL syntax errors, and improper handling of user input. Specifically, the case sensitivity of table names in MySQL means that Liam and liam are different and must be consistent. Additionally, using mysql_real_escape_string() helps avoid SQL injection, ensuring application security.
Complete Code Example
Below is a complete search page example that integrates the form and processing logic into a single file for ease of understanding and deployment.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Search Page</title>
</head>
<body>
<form action="" method="post">
Search: <input type="text" name="term" /><br />
<input type="submit" value="Submit" />
</form>
<?php
$db_hostname = 'localhost';
$db_username = 'demo';
$db_password = 'demo';
$db_database = 'demo';
$con = mysql_connect($db_hostname, $db_username, $db_password);
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_database, $con);
if (!empty($_REQUEST['term'])) {
$term = mysql_real_escape_string($_REQUEST['term']);
$sql = "SELECT * FROM Liam WHERE Description LIKE '%".$term."%'";
$r_query = mysql_query($sql);
if ($r_query) {
while ($row = mysql_fetch_array($r_query)) {
echo 'Code: ' . $row['Code'];
echo '<br /> Description: ' . $row['Description'];
echo '<br /> Category: ' . $row['Category'];
echo '<br /> Cut Size: ' . $row['CutSize'];
echo '<br /><br />';
}
} else {
echo 'Query error: ' . mysql_error();
}
}
mysql_close($con);
?>
</body>
</html>Conclusion and Best Practices
Implementing a robust database search function requires attention to multiple aspects: proper database connection, secure user input handling, consistent naming conventions, and clear error handling. Although this article uses the traditional MySQL extension, in actual projects, it is advisable to use modern extensions like MySQLi or PDO for better security and performance. By following these best practices, you can build efficient and secure search systems.