Finishing Up Our Configurations File - PHP

Now that we have created helper functions for easily storing and retrieving each of the major components of our query, you may be noticing that we are still missing a couple of rather important elements, such as the “SELECT” and “FROM” portions. Because of how greatly a SELECT statement can vary from query to query, even on the same set of data, we will predefine these portions of the SQL query in our “inc.conf.php” file as follows:

# define our base SQL SELECT statement
$GLOBALS['db_base_select'] = '      SELECT      pc_categories.name            AS category_name,
                              pc_sub_categories.name        AS sub_category_name,
                              pc_manufacturers.name         AS pc_manufacturer_name,
                              pc_parts.name                 AS part_name,
                              pc_parts.description          AS part_description';

# define base SELECT query for retrieving a total record count
# NOTE: query should retrieve a count of all records and store that count in array key "record_count"
$GLOBALS['db_base_count'] = ' SELECT      count( pc_parts.id )    AS record_count';     

# define base FROM portion of query
$GLOBALS['db_base_from'] = '  FROM  pc_parts, pc_manufacturers, pc_categories, pc_sub_categories';

# define base WHERE portion of query, to setup required JOINs
$GLOBALS['db_base_where'] = ' WHERE pc_parts.manufacturer_id      = pc_manufacturers.id
                        AND   pc_parts.sub_category_id      = pc_sub_categories.id
                        AND   pc_sub_categories.category_id = pc_categories.id';

As you can probably see, the above query elements are split into a couple of different parts. The first, “db_base_select”, is merely a barebones SELECT statement that specifies which fields to retrieve and under what name each field is retrieved. The second field, “db_base_count” is similar except for the fact that it retrieves a numeric value, or count(), of the total number of records retrieved for a given query. This count is named “record_count”, which will be important later. Next we have “db_base_from”, which simply stores the FROM portion of our SQL query –- specifying the names of the tables from which we will be retrieving our information. Lastly, we have our basic, required JOIN conditions which are stored in “db_base_where”.

Assembling the above pieces into a SQL query will result in the retrieval of every record listed within our parts database, along with the name of its corresponding “Category”, “Sub Category”, and “Manufacturer”.

The text that makes up these portions of the query is not so important, as it could change widely depending on the type of data we’re dealing with. The main achievement of these variables is that they contain only the data required to retrieve a meaningful set of results. Anything else, such as the LIMIT and ORDER BY portions of the query are left for our search tool to automate.

Let’s add a couple more variables to our “inc.conf.php” file now:

      # specify URL var. name for ORDER BY and
LIMIT (start) arguments
      $GLOBALS['db_order_by_field_name']        = 'order_by_field';
      $GLOBALS['db_limit_start_name']           = 'limit_start';

     # specify max. results-per-page number
      $GLOBALS['db_limit_stop_value']           = 10;

The first of the above groups of variables pertain to the field names of URL arguments. This will make a little more sense shortly, but for now it will suffice to say that our application needs a way of letting the user select which page of results to display, or in which order to sort records –- and these variables will enable us to do that in an easily configurable manner. This step isn’t entirely necessary, but it never hurts to make our application a little more customizable.

The bottom variable, “db_limit_stop_value” specifies the maximum number of records to display per search results page. If our search returns 40 resulting records then those results will be split apart and grouped into four separate pages.

Before moving on we need to make one more update to our configuration file, this one affecting the search array itself. For each field in our search array, we will want to add an array key named “search_pattern”. The purpose of this key may be apparent once it is displayed below, but if not we will come back to it shortly. Add the following lines to the configuration file, below the corresponding arrays:

      $categories_array['search_pattern'] =
'pc_sub_categories.category_id = <<field>>';
      $sub_categories_array['search_pattern'] =
'pc_parts.sub_category_id = <<field>>';
      $manufacturers_array['search_pattern'] =
'pc_parts.manufacturer_id = <<field>>';
      $keywords_array['search_pattern'] =
"( pc_parts.name LIKE '%<<field>>%' OR
pc_parts.description LIKE '%<<field>>%' )";

That’s it for now. We’re ready to go back to our “search.php” file for a while, but we will revisit the above variables soon.