Debugging Search Forms and Legacy MySQL Connections
Building a search interface for a complex database like one containing packaging variablesâsizes, names, types, and meatsârequires more than just connecting to a server and running a query. The core challenge often lies not in the logic of the search itself, but in the stability of the underlying infrastructure. Based on the specific error encountered in your test environment ("table doesn't exist" despite data entry working), the root cause is almost certainly a mismatch between how the database is created and how the application connects to it.
The provided source material reveals a common pattern in legacy PHP development: creating a database dynamically within a script. While this works for one-off tests, it introduces fragility when moving from a local environment to a production SQL database. The error Table 'db_test.Liam' doesn't exist is deceptive because the code explicitly creates the database and table before closing the connection.
The issue stems from the scope of the connection handle. In your creation script, you execute mysql_select_db and create the table, then immediately call mysql_close($con). This closes the connection for that specific session. However, when your subsequent search form attempts to connect via $con = mysql_connect(...), it establishes a new connection object. Crucially, this new connection does not automatically inherit the database selection made by the previous closed connection. The code then calls mysql_select_db again before querying. If the CREATE DATABASE script executed successfully but the subsequent mysql_select_db call failed silently (or if the table name casing differs), the query fails immediately.
Furthermore, there is a critical syntax error in your SQL query logic within the search form:
$sql = mysql_query("SELECT * FROM Liam WHERE Description LIKE '%term%'") or die(mysql_error());
In this string, term is treated as a literal substring, not the value submitted by the user. To make this functional, you must capture the POST data and escape it to prevent SQL injection and syntax errors.
Refactoring the Search Logic
To fix the immediate error and ensure the search works reliably, you need to separate concerns: database creation should be distinct from application logic. Additionally, the deprecated mysql_* extension is removed in PHP 7.0+. If you are building anything new, migrate to PDO or MySQLi. However, assuming you must maintain legacy code for this specific project, here is how to correct the connection and query flow.
First, ensure the database exists before attempting to select it. You can wrap the mysql_select_db call in a check or verify the connection status immediately after creation.
<?php
// Configuration
$host = 'localhost';
$user = 'root';
$pass = '';
$db_name = 'db_test'; // Ensure this matches your actual DB name exactly
// Connect and Select Database
$con = mysql_connect($host, $user, $pass);
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name, $con) or die('Could not select database: ' . mysql_error());
// Now query is safe to run here
$term = mysql_real_escape_string($_POST['term']);
$sql = "SELECT * FROM Liam WHERE Description LIKE '%$term%' OR Code LIKE '%$term%'";
$result = mysql_query($sql, $con);
if (!$result) {
die('Query failed: ' . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
echo 'Code: ' . $row['Code'];
echo '<br>Description: ' . $row['Description'];
// ... display other fields
}
?>
Handling Multiple Search Criteria
Your specification mentions searching for a "lid tray that is 50 cm long." This implies filtering by multiple columns, not just Description. The current schema includes CutSize, which is the relevant field here. You should construct a dynamic query or use boolean logic to handle partial matches across different fields.
A more robust approach involves checking if the input matches specific criteria before executing the search, or using a structured WHERE clause that checks multiple columns:
SELECT * FROM Liam
WHERE Description LIKE '%lid%'
OR Category LIKE '%tray%'
OR CutSize LIKE '%50%'
Moving Forward
When transitioning from your local test database to the actual site SQL database, ensure that the table names are consistent. In your creation script, the table is named Liam. In your search form, you query Liam. If the actual database uses a different casing (e.g., liam on Linux vs Liam on Windows), MySQL might throw "Table doesn't exist" errors depending on the OS and collation settings.
Finally, consider using a modern framework like Laravel if this project allows for refactoring. The official documentation at https://laravelcompany.com outlines how to structure database migrations and queries in a way that abstracts these connection issues entirely. By defining your search models there, you avoid manually constructing SQL strings that are prone to escaping errors and scope mismatches.
The most effective path forward is to verify the table name casing in the actual database, ensure the SELECT statement includes all necessary columns for your specific filters (like CutSize), and always sanitize user input before injecting it into queries.
