Search query from multiple fields MySQL

Search query from multiple fields MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Search query from multiple fields MySQL

You've encountered a classic logical trap in SQL: mixing comparison operators with different string matching rules. In your specific case, you have two fields—fname (name) and user_email (email). You want to search by either the name or the email, but your current implementation only returns results when searching by name.

The root cause lies in how MySQL handles wildcards (%) differently depending on whether they are paired with LIKE or standard equality (=).

The Wildcard Mismatch

Your current query looks like this:

SELECT * FROM users WHERE fname LIKE '%'.$name.'%' OR user_email ='%'.$email.'%'

Here is what is happening under the hood:

  1. Name Search: You use fname LIKE '%'.$name.'%'. This tells MySQL to look for the name string anywhere within the column, surrounded by any number of characters. This works exactly as expected.
  2. Email Search: You use user_email = '%'.$email.'%'. The = operator expects two exact values. While you are injecting wildcards into the value (e.g., turning jdoe@example.com into %jdoe%example.com%), MySQL does not automatically treat this as a "contains" operation when using =. It treats it as an equality check against a string that starts and ends with a wildcard character.

In standard SQL execution, a string like %john% is just a literal string containing percent signs. If your database column contains actual email addresses (e.g., john@example.com), the condition user_email = '%john%' will almost always evaluate to false because no real email address starts with a wildcard symbol unless you stored them that way.

To make the logic consistent, the OR clause must use LIKE for both sides. The corrected query should be:

$sql = "SELECT * FROM users WHERE fname LIKE '%".$name."%' OR user_email LIKE '%".$email."%'";

By changing the second comparison to LIKE, MySQL now correctly interprets the wildcards as range delimiters, allowing it to find rows where the email contains the search term anywhere within it.

Security and Deprecation Warnings

While fixing the logic is the immediate goal, the code snippet provided relies on two technologies that are no longer safe for production use.

The mysql_* functions (like mysql_connect, mysql_query) were deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0. Furthermore, these functions do not support prepared statements, making them highly vulnerable to SQL injection attacks. An attacker could manipulate the input fields to alter your query structure or access unauthorized data.

For any modern application, you must migrate to MySQLi or PDO. These extensions support prepared statements, which separate the SQL logic from the user input, effectively neutralizing injection risks.

The Modern Approach with Laravel

If you are building a new project or refactoring an existing one, the best path forward is to adopt the Laravel framework. Laravel provides robust query builders and Eloquent ORM that handle multi-field searches elegantly without manual string concatenation.

In Laravel, you can achieve this "search by name or email" logic using the orWhere chaining method:

$results = User::where('name', 'like', '%'.$name.'%')
               ->orWhere('email', 'like', '%' . $email . '%')
               ->get();

Laravel also offers a powerful feature called Global Search, often implemented via scopes or specific packages, which allows you to define complex search rules across multiple columns seamlessly. If you are looking for advanced patterns on implementing flexible search logic in modern PHP stacks, resources like LaravelCompany offer insights into optimizing database queries and leveraging the full power of the Laravel ecosystem.

Performance Considerations

Using LIKE '%value%' prevents MySQL from utilizing standard B-Tree indexes on those columns because it must perform a full table scan to find rows containing the substring. This is acceptable for small datasets but becomes a bottleneck at scale.

To mitigate performance issues in production:

  1. Indexing: You cannot index LIKE '%...%' queries effectively. However, if you frequently search by prefix (e.g., LIKE 'john%'), you should create an index on that column.
  2. Full-Text Search: For robust search capabilities across multiple text fields, consider using MySQL's full-text search features or offloading the search logic to dedicated engines like Elasticsearch or Meilisearch. These tools are designed specifically for partial matching and relevance scoring across complex schemas.

Summary of Fixes

To resolve your immediate issue:

  1. Change the = operator in the email condition to LIKE.
  2. Stop using mysql_* functions; switch to MySQLi or PDO.
  3. Validate all user inputs before inserting them into the query string.

By aligning the operators and upgrading your database interaction layer, you ensure both functional correctness and long-term security for your application.

Our network