SQL: Search: Free PHP & MySQL Tutorial

Topics Covered in This PHP & MySQL Tutorial:

Wildcard Searches, Searching with a Form

Exercise Overview

Database search functionality forms the backbone of modern web applications—from e-commerce product catalogs to customer management systems. While there are countless sophisticated search techniques available, mastering wildcard searches provides the fundamental building blocks for more advanced implementations. In this exercise, we'll build a robust search interface that demonstrates how to safely perform wildcard queries using parameterized statements, ensuring both functionality and security.

Simple Search

The foundation of any wildcard search lies in the SQL LIKE operator combined with strategic wildcard placement. To search for a user by email, the syntax would be:

SELECT * 
FROM users
WHERE email LIKE '%mySearchTerm%'

This query will match any record in the database where the email field contains the specified search term. The % wildcard characters on either side create a flexible pattern that matches any text before or after the user's input. For example, searching for "john" would return emails like "john.doe@company.com", "johnny@example.org", or "info@johnsonlaw.net".

Now let's implement this search functionality through a practical, hands-on approach:

1. Open search.php from the search folder.

2. In your browser, navigate to the search interface:

   • Mac: localhost:8888/phpclass/search/search.php
   • Windows: localhost/phpclass/search/search.php

   You'll see a streamlined search form with a single email input field. This form submits to searchResults.php, which handles the database query and displays matching results in a formatted table.

3. Return to your code editor to examine the backend implementation.

4. Open searchResults.php from the search folder.

   The foundational structure is already in place—including database connection, SELECT statement, and result output formatting. Our task is to enhance this with wildcard functionality and ensure the user input is properly sanitized for the parameterized query.

5. Locate line 5 and modify the $SQL variable by adding the highlighted WHERE clause:

   $SQL = 'SELECT id, firstName, lastName, email, publications, comments, subscribe 
   FROM users
   WHERE email LIKE ?
   ';

   This modification transforms our query from selecting all records to filtering results based on email pattern matching using the LIKE operator with a parameterized placeholder.

Modifying the Search Term

Here's where many developers encounter a crucial implementation detail: wildcard characters cannot be embedded directly within the SQL query when using parameterized statements. Instead, we must append the percent signs to the search term itself before binding the parameter. This approach maintains the security benefits of parameterized queries while enabling flexible pattern matching.

1. Create the wildcard-enabled search term by concatenating percent signs to the form input. Add this code above the $SQL variable declaration:

   $searchTerm = '%'. $_POST['email']. '%';

   This line captures the email value submitted from the form ($_POST['email']) and wraps it with wildcard characters, creating a flexible search pattern that matches partial email addresses.

2. Complete the implementation by binding the search parameter to the prepared statement. Around line 14, locate the //bind params here comment and replace it with:

   $stmt->bind_param('s', $searchTerm);

   This critical step binds our $searchTerm variable to the query placeholder, with the 's' parameter indicating we're passing a string value. This approach prevents SQL injection attacks while maintaining search flexibility.

3. Save your changes and test the implementation by returning to your browser:

   • Mac: localhost:8888/phpclass/search/search.php
   • Windows: localhost/phpclass/search/search.php

4. Experiment with both complete and partial email addresses to verify the wildcard functionality works as expected. Try searching for domain names, usernames, or partial matches to see the full power of your implementation.

5. Return to your code editor to review the completed functionality.

6. Close any open files. You've successfully implemented a secure, flexible wildcard search system!