Creating a Searchable Inventory System: Setting Up Your Database and User Interface (continued)

In this second part of an article about setting up a searchable inventory system, we will cover our helper Search class, and some of the things this class will need to be able to do.

In part one of this article we discussed setting up the basic elements required for our searchable inventory system: the backend database structure, the HTML and CSS code necessary to create our visual layout, and a helper Javascript function “attach_file” which we will be taking a closer look at later.

So far, so good. Now that our overall program design has been created, we’re ready to start narrowing things down and we will start by thinking about our helper Search class. There is a variety of things this class will need to do, but for part two of this article we are only concerned with displaying a proper set of search form fields.

We will need a function within our Search class to display appropriate form field objects for each of the items by which we wish to search. Let’s call this function “get_search_field”, and as a parameter let’s pass it the name of the field for which we wish to create an input field.

{mospagebreak title=Helper Search Class}

To start things off, our search.php file should contain the following:

<?php
class search {

      function get_search_field( $p_search_field_name ) {
            $r_html     = NULL;           

            # return HTML
            return $r_html;           

      } # END get_search_field()

     } # END class search
?>

The next step is to think about the information this function will need in order to operate properly. First, it will need to know the type of form field we wish to create (SELECT menu or text input). If we wish to create a SELECT menu, it will also require some additional information, such as the SQL query needed to retrieve a list of SELECT options. Since we are creating several of these select menus to depend on another menu, we will also need to tell the “get_search_field” function whether a field is dependent upon itself, or another field.

This can all be done through the use of associative arrays. Furthermore, our “inc.conf.php” file is a great place to do all of this, so let’s go ahead and set up the arrays by adding the following code:

# configure various search fields
# first, instantiate over-all search array
$search_array                             = array();

# configure and append categories array
$categories_array                         = array();
$categories_array['field_type']           = ‘select';
$categories_array['populate_query']       = ‘SELECT id AS value,
name AS display FROM pc_categories ORDER BY name ASC;';
$categories_array['parent_menu']          = NULL;
$categories_array['html_attributes']      = ‘onChange=”attach_file( ‘helper.php?category_id=’ +
this.options[ this.selectedIndex ].value );”‘;

# configure and append sub categories array
$sub_categories_array                     = array();
$sub_categories_array['field_type']       = ‘select';
$sub_categories_array['populate_query']   = ‘SELECT id AS value,
name AS display FROM pc_sub_categories WHERE category_id =
<<field>> ORDER BY name ASC;';
$sub_categories_array['parent_menu']      = ‘Categories';
$sub_categories_array['html_attributes']  = NULL;

# configure and append manufacturers array
$manufacturers_array                      = array();
$manufacturers_array['field_type']        = ‘select';
$manufacturers_array['populate_query']    = ‘SELECT id AS value,
name AS display FROM pc_manufacturers WHERE category_id =
<<field>> ORDER BY name ASC;';
$manufacturers_array['parent_menu']       = ‘Categories';
$manufacturers_array['html_attributes']   = NULL;

# configure and append keywords array
$keywords_array                           = array();
$keywords_array['field_type']             = ‘text';
$keywords_array['parent_menu']            = NULL;
$keywords_array['populate_query']         = NULL;
$keywords_array['html_attributes']        = NULL;

# add each field to main search array
$search_array['Categories']               = $categories_array;
$search_array['Sub Categories']           = $sub_categories_array;
$search_array['Manufacturers']            = $manufacturers_array;
$search_array['Keywords']                 = $keywords_array;

# set search array to be global
$GLOBALS['search_array']                  = $search_array;

As you can see, we have created a GLOBAL array, “search_array”, and added within that array several other arrays –- each describing a particular field of our search form. Each field for instance specifies a “file_type”, either “select” or “text”. If the field is of type “select” then the array also specifies the query that will be required to retrieve a list of all possible SELECT values, and so forth. We’ll take a closer look at each of these elements as we create our helper Search class, which we are now ready to start on.

{mospagebreak title=Our First Helper Search Function}

Since any web search begins with a search form, it is appropriate that our helper Search class begins with the “get_search_field” function. We’ve already defined all of the information required to create that function within our “inc.conf.php” file, so let’s take a look at the code and how it will work:

      function get_search_field( $p_search_field_name ) {
            $r_html     = NULL;

            # retrieve search field information from GLOBALS
array
            $f_search_array   = $GLOBALS['search_array']
[ $p_search_field_name ];
           

            # check session for pre-set search field value
            $f_input_value    = $this->get_field_session_value
( $p_search_field_name );           

            # retrieve any additional pre-set HTML attributes
            $f_html_attributes      = $f_search_array
['html_attributes'];           

            # properly format field name before continuing
            $f_search_field_name    = str_replace( ‘ ‘, ‘_’,
$p_search_field_name );       

            # if field is of type select, then return a SELECT
input field with all appropriate options
            if ( $f_search_array['field_type'] == ‘select’ )
{           

                  # open select tag
                  $r_html     .= “<SELECT
NAME=’$f_search_field_name’ ID=’$f_search_field_name’
$f_html_attributes /> rn”;                 

                  # append (empty) “Any” option
                  $r_html     .= “<OPTION value=”>Any</OPTION>
rn”;                 

                  # retrieve all appropriate OPTIONs from DB
using specified query
                  # NOTE: replace placeholder “<<field>>” with
appropriate parent-select-menu value if applicable
                  $db_result  = mysql_query( str_replace
( ‘<<field>>’, $this->get_field_session_value( $f_search_array
['parent_menu'] ), $f_search_array['populate_query'] ) );
                  if ( @ mysql_num_rows( $db_result ) ) {
                        while ( $db_record = mysql_fetch_assoc
( $db_result ) ) {           

                              # retrieve SQL values
                              $l_option_value         =
$db_record['value'];
                              $l_option_display = $db_record
['display'];                             

                              # check to see if current option
has been selected
                              $l_selected = ( $l_option_value ==
$f_input_value ) ? ‘selected’ : NULL;           

                              # append OPTION tag
                              $r_html     .= “<OPTION value=’$l_option_value’ $l_selected>$l_option_display</OPTION>
rn”;                       

                        } # END for each result                 

                  } # END if results found                 

                  # close select tag
                  $r_html     .= “</SELECT> rn”;           

            # if field is of type text, then simply return a TEXT
input field
            } else {           

                  # return a text input field
                  $r_html     .= “<INPUT TYPE=’text’
NAME=’$f_search_field_name’ ID=’$f_search_field_name’
VALUE=’$f_input_value’ $f_html_attributes />”;

           } # EBD ‘field_type’ check           

            # return HTML
            return $r_html;           

      } # END get_search_field()

As you can see, we not only completed the “get_search_field” function, but we also introduced an additional helper function, “get_field_session_value”. Let’s take a quick look at it, and its matching “set_field_session_value” function, before continuing:     

      function get_field_session_value( $p_search_field_name ) {
            return $_SESSION[ 'search_' . $p_search_field_name ];
      } # END get_field_session_value()     

      function set_field_session_value( $p_search_field_name,
$p_search_field_value ) {
            $_SESSION[ 'search_' . $p_search_field_name ]   =
$p_search_field_value;
      } # END set_field_session_value()

The purpose of the “get_field_session_value” function is simple: to retrieve a user-defined value for the particular field of choice, if any, from the SESSION. As the user runs a search, the value specified for each search field will be stored within the SESSION (using “set_field_session_value”). Then as the page is re-drawn PHP will pre-select the value(s) previously provided by the user. This step is not necessary, but will help to avoid any confusion in the mind of the user as he or she uses our search form.

Now back to the “get_search_field” function. As you can see, this function is comprised mostly of comments, so it should be relatively self explanatory. First we retrieve the array from the configuration file, then using the values we have defined for each menu object, we create the appropriate field. If the field should be a SELECT menu, we also retrieve a list of appropriate OPTION tags using the query specified in the configuration array.

There are only a couple of items which may cause confusion. The first deals with the SELECT menu query, “pre_populate”. In order to keep our helper function(s) as simple as possible, this query must retrieve its resulting SQL information in the form of two fields: “value” and “display”. The “value” field is used to populate the “value” attribute of our HTML OPTION tag, while the “display” field is used to actually display the information to the user. In other words, if the query retrieves a record with the display field of “Bob” and a value of “3”, “Bob” is what is shown to the user but “3” is the value submitted when a new search is triggered.

Secondly, two of our fields specify a “parent_menu” name, “Categories”. As our helper function retrieves the SQL query used to populate these search menus with a list of OPTION tags, it also checked the “parent_menu” field’s session value (using the helper function “get_field_session_value”). Since our “Sub Categories” and “Manufacturers” menus depend on values found within “Categories”, we don’t want to load them by default –- but instead, only when a “Categories” value is provided. However, if a “Categories” value was provided in a previous search, then we want that value to be open by default as the page is displayed. By replacing a special placeholder string, “<<field>>”, with any pre-specified value of the parent “Categories” menu, we are able to ensure that the appropriate list of OPTIONs is initially displayed.

This brings us to our next point. Some of our “populate_query” values contain the sub-string “<<field>>”. As you may have noticed, our helper function replaced that string with a dynamic value at run-time. The purpose of this is to allow our SELECT menus the option of being dependent upon each other using user-specified values. Our query, then, sets up all of the information necessary to allow for that interaction, except for the actual value of the key itself. That is provided at run-time as the user selects a “Categories” menu option.

Finally, our function removes any potential spaces from a field name before creating a corresponding HTML entity. This is done to prevent breakage in the way POST values are submitted for fields containing spaces in their names.

You may see the effects of our “get_search_field” function by inserting the following code into the “index.php” file. (This code should be inserted where the previous “<!– … Menu here –>” placeholders were located.)

      <?php $search = new search(); ?>
      <td>
            <?php echo $search->get_search_field
( ‘Categories’ ); ?>
      </td>
      <td>
            <?php echo $search->get_search_field( ‘Sub
Categories’ ); ?>
      </td>
      <td>
            <?php echo $search->get_search_field
( ‘Manufacturers’ ); ?>
      </td>
      <td>
            <?php echo $search->get_search_field( ‘Keywords’ ); ?>
      </td>

{mospagebreak title=Dynamic SELECT Menus}

Our search form is almost completed, with one exception: the dynamic SELECT menus do not yet work. As you may have noticed, our “Categories” menu specified an additional HTML attribute. This attribute called our JavaScript function, “attach_file”, whenever its onChange event was triggered.

onChange=”attach_file(  ‘helper.php?category_id=’ +
    this.options[ this.selectedIndex ].value );”

Another way of stating the above code would be to say that each time a “Categories” option is selected, the “helper.php” file is called and as a parameter, it receives the ID value of the category selected. In order to complete our search form then, we will need to complete the “helper.php” file by providing the following code:

<?php
      # set appropriate content type – to tell the browser we’re
returning Javascript
      header( ‘Content-Type: text/javascript’ );     

      # include all additional files
      include_once ‘inc.core.php';     

      # retrieve Category ID from URL
      $category_id      = mysql_escape_string( $_GET
['category_id'] );     

      # update child select menus
      update_child_select(    ‘Sub Categories’, $category_id );
      update_child_select(    ‘Manufacturers’,  $category_id );     

      # helper function to automate child menu update
      function update_child_select( $p_search_field_name,
$p_db_key_val ) {
            $f_search_array   = $GLOBALS['search_array']
[ $p_search_field_name ];
     

?>
            // set quick reference to child menu object
            var child_menu_obj      = document.getElementById( ‘<?php echo str_replace( ‘ ‘, ‘_’, $p_search_field_name ); ?
>’ );     

            // erase all current (child) SELECT menu options
            while ( 0 < child_menu_obj.options.length )
                  child_menu_obj.remove( 0 );                 

            // add empty “Any” option to menu
            var option_obj = new Option( ‘Any’, ” );
            child_menu_obj.options[ 0 ] = option_obj;
<?php     

            # if a category has been specified
            if ( !empty( $p_db_key_val ) ) {           

                  # run SQL query to return all child menu options
                  $db_query   = str_replace( ‘<<field>>’,
$p_db_key_val, $f_search_array['populate_query'] );
                  $db_result  = mysql_query( $db_query );                 

                  # outout a SELECT menu option for each row
returned
                  if ( $db_result ) {
                        while ( $l_db_row = mysql_fetch_assoc
( $db_result ) ) {
                       

                              # show all retrieved (child) SELECT
menu options
                              $l_child_value          = $l_db_row
['value'];
                              $l_child_display  = $l_db_row
['display'];                             

                              # add new option to child menu
?>
                              var option_obj = new Option( ‘<?php
echo $l_child_display; ?>’, ‘<?php echo $l_child_value; ?>’ );
                              var option_rank =
child_menu_obj.options.length;
                              child_menu_obj.options
[ option_rank ] = option_obj;
<?

                        } # END while results
                  } # END if category
            } # END if results     

      } # END update_child_select()
?>

As you may have noticed, the above code is a mix of PHP and JavaScript. This is because our PHP file is being attached to the document as a JavaScript file. It is therefore capable of executing not only PHP code but JavaScript as well. This is a very important concept, as it will allow us to not only retrieve a list of appropriate values from our SQL database, but also dynamically update the search form’s SELECT menus with those values.

All our file does, in a nutshell, is retrieve the Category ID from the URL, run a query to retrieve all “Sub Category” and “Manufacturer” records found belonging to that ID, and then update the SELECT menus to contain those new values. This is made a little cleaner through the use of our helper function, “update_child_select”, but should still be relatively self-explanatory. Additional information on this technique may be found in my recent two-part article posted on Dev Articles entitled “PHP and JavaScript — Pooling Resources”.

Note: As before, we remove any spaces present in the field name before retrieving the HTML element object.

In Summary

At this point, our page layout has been set up using HTML and CSS, our code flow has been defined, and our helper Search class has been initiated. Our application now has all of the logic necessary to intelligently create a search form for users to input their search criteria. What’s more, this logic is all easily reusable and requires little more than updating the logic found within our “inc.conf.php” (and possibly the “helper.php”) files.

This is a good start, as we have now laid the ground-work for part three of this article, which will cover retrieving a list of result records and sorting those records by each field specified. The real meat of the application lies there, but it is important to first establish an easily extensible interface with which users may conduct searches. So far we are right on track.

If you would like to see a working example of this application, you may do so here: http://portfolio.boynamedbri.com/devshed/search/part_2/. If you would like to download source code for this application, you may access that here: http://images.devshed.com/ds/stories/Creating_Searchable_Inventory_System/
part_2.zip

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan