Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Integrating PHP with Databases

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Creating the Search Function

3:10 with Alena Holligan

Let’s create the function that actually pulls the items using our search term.

SQL Like Operator
SQL Wildcards

Example Code

function search_catalog_array($search, $limit = null, $offset = 0) {
    include("connection.php");

    try {
       $sql = "SELECT media_id, title, category,img 
         FROM Media
         WHERE title LIKE ?
         ORDER BY 
         REPLACE(
           REPLACE(
              REPLACE(title,'The ',''),
              'An ',
              ''
           ),
           'A ',
           ''
         )";
       if (is_integer($limit)) {
          $results = $db->prepare($sql . " LIMIT ? OFFSET ?");
         $results->bindValue(1,"%".$search."%",PDO::PARAM_STR);
          $results->bindParam(2,$limit,PDO::PARAM_INT);
          $results->bindParam(3,$offset,PDO::PARAM_INT);
       } else {
         $results = $db->prepare($sql);
         $results->bindValue(1,"%".$search."%",PDO::PARAM_STR);
       }
       $results->execute();
    } catch (Exception $e) {
       echo "Unable to retrieved results";
       exit;
    }

    $catalog = $results->fetchAll();
    return $catalog;
}

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    We changed our count function to count the results of the search term. 0:00
    While we're in this functions file, 0:04
    let's create the function that actually pulls the items using our search term. 0:06
    We'll duplicate the category, catalog array. 0:10
    And we'll name this search_catalog_array. 0:27
    Instead of the argument category, we'll wanna change that to search. 0:31
    Then we're ready to replace the WHERE statement as we did above. 0:36
    We want to search for where our title is like our search term. 0:45
    Finally, we'll change the first bindParam to bindValue. 0:52
    Then we'll change this out for our search. 1:01
    We need to make sure we include our wildcards. 1:04
    We also need to change this bindParam to bindValue in our else statement. 1:10
    We can also remove the line at the top that changes our category to lower case. 1:23
    By default, the like operator is not case sensitive. 1:30
    Let's go back to our catalog.php file. 1:34
    And start using these new functional changes. 1:36
    When setting total items, we can now pass both the section and 1:39
    the search to our function. 1:43
    One of them will be null and that's okay. 1:46
    All this stays the same until we scroll down to setting the catalog variable. 1:49
    We're going to add another conditional. 1:55
    We want to check if our search term is not empty. 2:02
    Again, make sure you add else to the second if statement. 2:12
    If our search term is not empty, we want to set 2:17
    the catalog equal to our search_catalog_array. 2:21
    We'll pass in our search term, 2:27
    the items_per_page, And our offset. 2:30
    Let's view this in the browser and perform a search. 2:38
    You can see that it's mostly working. 2:45
    We can see a list of results that have been filtered by our search term. 2:48
    But what happens if nothing matches our search results? 2:51
    We get redirected to the full catalog. 2:56
    Now, that's confusing. 2:58
    We need to let our users know that there are no search results. 3:00
    We also wanna change your header so that they know that they are seeing the search 3:03
    results, and not the full catalog page. 3:06

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up