Home arrow PHP arrow Page 3 - Creating a Searchable Inventory System: Retrieving and Managing Search Results

Finishing Up Our Configurations File - PHP

In this third article in our series, we will look at the type of functions for which we need to use our search information, then take a look at retrieving records based on various search criteria.

  1. Creating a Searchable Inventory System: Retrieving and Managing Search Results
  2. Methods of Storing Search Criteria
  3. Finishing Up Our Configurations File
  4. Forming the Basic Query
By: Brian Vaughn
Rating: starstarstarstarstar / 6
November 22, 2005

print this article



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.

>>> More PHP Articles          >>> More By Brian Vaughn

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: