Skip to main content
Noble Desktop Publishing Team/3 min read

SQL: Search

SQL Search with LIKE

Wildcard Syntax

WHERE email LIKE '%mySearchTerm%' — % matches any text before/after.

Single-Char Wildcard

Underscore (_) matches exactly one character. WHERE name LIKE 'J_n' matches Jan, Jen, Jon.

Use Prepared Statements

$stmt = $conn->prepare("... LIKE ?"); $stmt->bind_param('s', '%'.$term.'%');

Form Submits to Results Page

Simple form posts to searchResults.php; that page runs the query and displays matches.

Build Back-End Skills at Noble Desktop

Noble Desktop's Full-Stack Web Development Certificate teaches modern back-end development — concepts that carry across PHP, Node.js, and Python.

Deepen your understanding of PHP and MySQL through this comprehensive tutorial that covers topics like wildcard searches and forms, and provides hands-on exercises for practical learning.

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!