SQL: Search

Topics Covered in This PHP & MySQL Tutorial:

Wildcard Searches, Searching with a Form

Exercise Overview

There are an enormous number of ways to search for information in a database—far more than can be covered in this book. We’ll show how to perform a basic wildcard search on a column.

Simple Search

To search for a user by email, the syntax would be:

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

This will match any record in the database that has an email that contains text in the variable mySearchTerm. The % signs on either side of the search are the wildcard filters and mean to match any text before or after what the user enters.

1. Open search.php from the search folder.

2. In a browser go to:

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

   It’s just a simple form with one input for an email. It submits to a page called searchResults.php which is the page that will perform the search and display the results.

3. Switch back to your code editor.

4. Open searchResults.php from the search folder.

   Most of the page has already been written. It contains a SELECT statement and outputs the results to a table. We’ll add the wildcard filters and format the user input so it is compatible with the parameterized query.

5. Around line 5, modify the $SQL variable by adding the bold text as shown below:

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

   This will filter the results to only display records that are LIKE the bound parameter.

Modifying the Search Term

Note in the syntax example at the beginning of the exercise that the search term is surrounded by percent signs. PHP won’t allow those percent signs to be put directly in the SQL—rather they need to be added to the search term itself.

1. CONCATENATE percent signs to the $_POST['email'] that is submitted from the search form. At the top of the page above the $SQL variable, add the following bold code:

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

   This takes the email that is submitted from the form ($_POST['email']) and adds percent signs on either side of it.

2. Finally we need to bind this parameter to the query. Around line 14 find the //bind params here comment and replace it with the bold code:

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

   This binds the $searchTerm variable and tells PHP to expect a string.

3. Save the page and then in a browser go to:

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

4. Enter a full or partial email and test it out. Excellent!

5. Switch back to your code editor.

6. Close any open files. That’s all folks!