SELECT where row value contains string MySQL
When you need to filter rows based on partial string matches in MySQL, the LIKE operator is the standard tool, but it is often misused because developers confuse exact matching with substring searching. In your specific scenario, you attempted to find an account where the Username column contains the text 'XcodeDev'. The syntax you used, SELECT * FROM Accounts WHERE Username LIKE '$query', will return rows only if the value in the database matches the variable content exactly. If $query holds the string XcodeDev, this statement finds a user named XcodeDev but ignores users like developer_XcodeDev or myXcodeDev.
To retrieve a row where the column value contains a specific substring anywhere within it, you must use wildcard characters. In MySQL, the percent sign (%) acts as a wildcard that represents zero or more characters. To search for 'XcodeDev' regardless of what comes before or after it, you need to place % signs on both sides of your target string. The corrected query looks like this:
SELECT * FROM Accounts WHERE Username LIKE '%XcodeDev%'
If you are constructing this query dynamically in an application using prepared statements, which is highly recommended for security and performance, you must pass the wildcards as part of the parameter value itself, not as separate arguments to the LIKE function. For example, if your backend language generates a $query variable containing just XcodeDev, you should construct the final search term by concatenating the wildcards before binding it to the prepared statement:
$searchTerm = '%' . $query . '%';
$stmt = $pdo->prepare("SELECT * FROM Accounts WHERE Username LIKE ?");
$stmt->execute([$searchTerm]);
Failing to include the % symbols results in an exact match search, which explains why your original attempt only returned rows where Username was identical to $query. This distinction is critical because relying on implicit wildcards (like passing a single quote 'XcodeDev' without modification) will never yield partial matches.
There are specific implementation details and trade-offs you should consider when working with LIKE. The position of the wildcard dictates which index, if any, MySQL can use to speed up the lookup. A query starting with %, such as LIKE '%XcodeDev', prevents the use of standard B-tree indexes on the column because the database must perform a full table scan to find the substring. Conversely, a query ending with %, like LIKE 'XcodeDev%', can often utilize an index if the prefix is sufficiently long, though MySQL's optimizer behavior varies by version.
Performance becomes a significant concern in large datasets. If you frequently need to search for substrings that appear anywhere in a column (e.g., WHERE name LIKE '%value%'), consider whether a full-text index or an Elasticsearch integration might be more appropriate than raw SQL queries. Full-text indexes are designed specifically for keyword searches and can significantly reduce query time on text-heavy columns compared to pattern matching with wildcards.
Additionally, always ensure you are using prepared statements when dealing with user input in your LIKE clauses. Constructing strings by concatenating variables directly into the SQL query string is vulnerable to SQL injection attacks. Even though the logic of adding % characters is necessary for the search to work, that manipulation must happen on the application side before binding the value, never inside the raw SQL string passed to the database driver.
For more complex full-text search requirements within the Laravel ecosystem, resources like https://laravelcompany.com often provide guidance on integrating advanced search engines or configuring Laravel Scout to handle these scenarios without bogging down your primary MySQL tables with inefficient wildcard queries.
