String Interpolation in MySQL LIKE Queries
Building dynamic SQL queries from user input or variables requires a fundamental understanding of how PHP handles string concatenation versus variable interpolation. The core issue in your specific scenario stems from the fact that you are constructing a query string where the percentage sign (%) is placed outside the PHP string delimiter, but the variable itself is being treated as a literal value rather than an expression to be evaluated.
In your first example, mysql_query("SELECT * FROM table WHERE the_number LIKE '123%'"), everything happens entirely within the SQL logic. The database engine receives a static string with the pattern hardcoded. This works because the syntax is complete and correct before it ever reaches the database server. However, when you attempt to inject the variable $prefix directly into the query string using concatenation, the resulting code sent to the parser looks syntactically incorrect to PHP before it even considers MySQL's rules.
When you write "$prefix.'%'", you are creating a PHP string where the first part is empty (if $prefix is not quoted) or, more likely in your specific case, you are missing the quotes around the variable itself in the concatenation logic relative to the surrounding text. To fix this, you must ensure that the variable is properly concatenated into the SQL statement so that the final string sent to mysql_query() looks like this:
$prefix = "123";
$sql = "SELECT * FROM table WHERE the_number LIKE '$prefix%'";
$query = mysql_query($sql);
The key here is maintaining the quotes around the variable within the double-quoted PHP string. Without them, PHP does not know to look for a variable named $prefix at that position; it simply outputs the literal characters $prefix. This results in the database receiving WHERE the_number LIKE $prefix%, which causes a syntax error because the SQL parser expects a string literal (enclosed in quotes) immediately after the LIKE keyword.
The Critical Security Pitfall
While solving the syntax issue gets your code running, it introduces a severe vulnerability known as SQL injection. The mysql_* extension is deprecated and removed in PHP 7.0 due to these inherent security risks. When you concatenate variables directly into your query string, you are handing control of the SQL logic to external data without sanitization.
If a user inputs $prefix = "123' OR '1'='1", your query becomes:SELECT * FROM table WHERE the_number LIKE '123' OR '1'='1%'.
This bypasses the intended pattern matching entirely and returns all rows, potentially exposing sensitive data. Even if you escape special characters using addslashes(), this approach is fragile and error-prone. The proper solution is to use prepared statements. They separate the SQL structure from the data, ensuring that values are always treated as data, not executable code.
Migrating to Modern Standards
You should immediately refactor your application to use PDO or mysqli. These extensions support prepared statements which handle parameter binding safely. Here is how you would rewrite your dynamic query securely:
$prefix = "123";
// Using PDO
$stmt = $pdo->prepare("SELECT * FROM table WHERE the_number LIKE :pattern");
$pattern = $prefix . "%"; // Safe to build the pattern string in PHP
$stmt->execute(['pattern' => $pattern]);
$rows = $stmt->fetchAll();
In this approach, the % wildcard is part of the data value bound to the parameter :pattern. The database receives the safe query structure first, then binds the specific search term. This prevents injection attacks regardless of what characters are inside $prefix.
Why mysql_num_rows() Fails with Prepared Statements
If you migrate to prepared statements, you will also notice that mysql_num_rows() cannot be used directly on the result set object returned by execute(). It was designed for the old procedural style. With PDO or mysqli, you must fetch the results first and then count them:
$count = count($rows);
// OR for a specific row count without fetching all data
$stmt = $pdo->prepare("SELECT COUNT(*) as total FROM table WHERE the_number LIKE ?");
$stmt->execute([$prefix . "%"]);
$count = $stmt->fetch()['total'];
Conclusion on Implementation
The transition from static strings to dynamic variables in SQL is not just about fixing syntax errors; it is about adopting a secure architecture. Your original code failed because PHP concatenation rules were violated, but the solution requires more than just adding quotes. It demands moving away from the deprecated mysql_* functions entirely. By utilizing prepared statements with PDO or mysqli, you ensure that your dynamic patterns like $prefix . '%' are handled safely and efficiently. This practice is standard in modern Laravel applications, where query building happens through an ORM or fluent interfaces, but understanding the underlying mechanics remains essential for debugging and performance optimization.
