Forming the Basic Query - PHP

We’re now ready to move onto the really fun part –- setting up our query. Let’s go ahead and add the following code to our “search.php” file, and then we’ll take a closer look at what it’s doing:

      function get_search_results() {
            $r_return_array   = array();           

            # if more than one record has been found, retrieve records
            if ( $this->get_total_record_count() > 0 ) {
                  # retrieve base query from config. file

                  $f_db_query = $GLOBALS['db_base_select'] . ' ' . $GLOBALS['db_base_from'] . ' ' . $GLOBALS['db_base_where'];                 

                  # retrieve and append any additional WHERE query data
                  $f_db_query .= $this->get_where_constraints_value();                 

                  # retrieve and append any additional ORDER BY query data
                  $f_db_query .= ' ORDER BY ' . $this->get_order_by_field() . ' ' . $this->get_order_by_direction();               

                  # retrieve and append additional LIMIT (start) information
                  $f_db_query .= ' LIMIT ' . $this->get_limit_start_value() . ', ' . $GLOBALS['db_limit_stop_value'];                 

                  # run query, and retrieve records
                  $db_result  = mysql_query( $f_db_query );
                 

                  # return record array result set
                  if ( @ mysql_num_rows( $db_result ) > 0 ) {

                        while ( $l_db_result    = mysql_fetch_assoc( $db_result ) )
                              $r_return_array[] = $l_db_result;
                  } # END if results returned
            } # END if more than one record found           

            # return array of results
            return $r_return_array;           

      } # END get_search_results()

Our “get_search_results” function starts by checking the total record count (set by the soon to be introduced “init_query” function). If at least one record was found, the function continues on to retrieve our base query elements, as previously discussed, from the configuration file. It then retrieves and tacks on any additional search criteria using another helper function, “get_where_constraints_value”. (We’ll take a closer look at “set_where_constraints_value” in a moment.) This allows us to not only retrieve a meaningful set of records (using the SELECT and JOIN criteria we provided earlier) but also restrict the records we retrieve using the user-provided search criteria.

Once the WHERE portions of our query have been completed, we next add the ORDER BY portion using two more of the helper functions we defined above. This allows us to sort the records by the user’s field of choice, something we will also review shortly.

Lastly, we add the LIMIT portion of our query, using another helper function along with the “Max. Search Results Per Page” variable we set in our configuration file. This will allow us to retrieve one “page” worth of results at a time.

Now that our query is fully formed, the remainder of our function simply runs it and stores any results as associative arrays contained within a larger array. This array is then returned, allowing the function that called it to operate on the resulting records in whatever manner it chooses. This is an important step in making our search class reusable, as different types of data will need to be displayed in different ways. Because of this, it’s best to separate the programming logic from the output of actual results and HTML. In other words, our Search class will retrieve the results, but our “index.php” file will choose how to actually display them.

Summary

So far we’ve covered a lot of new ground with this article. We’ve introduced several new helper functions, added some new variables to our configuration file, and created the logic to piece together our dynamic search query. The pieces are beginning to fall together, and things will only pick up from here.

This is a good place to break, but we’ll pick right back up with things in the next article, “Retrieving and Managing Search Results, Continued…”. In the meantime, if you have not been following along you may still download the source code for this application here: http://portfolio.boynamedbri.com/devshed/
search/part_3/part_3.zip