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

Businesses that hope to sell items through their website need a searchable inventory system for the web. This article, the first in a four-part series, will start you on your way to building one.

There are many sites on the web that employ searchable inventory systems. Some do a better job than others, and as web developers it is our goal to create those systems that rank among the highest in usability and user friendliness.

This article will be a four-part tutorial on creating a searchable inventory system for the web. We will take a look at everything –- from initial database design and creation, to user interface layout with CSS and HTML, to the backend PHP and JavaScript code that pulls everything together. In this first part, we will discuss setting up the database and the visual elements of the search (the overall HTML layout, CSS, JavaScript, and so on).

Part two will introduce our helper Search class, and talk about creating a set of inter-dependent SELECT menus using a dynamically included JavaScript file. Part three will deal with constructing our search query, displaying results, sorting results, and proper storage of search field values within the SESSION. Finally, part four will seal things off with some advanced search features such as a pagination menu, the option to “Search within Results,” and the choice between multiple view-modes for display.

By the end of this series of articles you should be able to apply the techniques discussed in order to create an effective, easy to use interface for searching through any type of online inventory.

{mospagebreak title=A Look at the Database}

The most fundamental element of any online inventory system, and perhaps the most important, is the database itself. The database contains the information that drives the rest of the site (such as product specs, availability, prices, and so on). Its layout in turn affects the usability of the information it contains. A properly designed database can be utilized in many ways, while a poorly designed database can be almost worthless.

We will begin our searchable inventory project by taking a look at the database structure itself. For our example project we will create an inventory system of computer hardware components. For simplicity’s sake, we will not store extensive information as would be used in a typical online storefront, but will store only the basic details (name, a brief description, product manufacturer, and part type). After all, the concepts we will be learning are not limited to computer parts, but rather deal with the overall techniques we will be applying to search through those components.

Our database will comprise four tables, and may be created by running the following SQL queries:

– holds category info: “motherboards”, “memory”, etc.
CREATE TABLE `pc_categories` (
  `id` int(2) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default ”,
  PRIMARY KEY  (`id`)
);

– holds manufacturer info: “ASUS”, “MSI”, etc.
CREATE TABLE `pc_manufacturers` (
  `id` int(3) unsigned zerofill NOT NULL auto_increment,
  `category_id` int(2) unsigned zerofill NOT NULL default ’00’,
  `name` varchar(25) NOT NULL default ”,
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)
);

– holds part info
CREATE TABLE `pc_parts` (
  `id` int(5) unsigned zerofill NOT NULL auto_increment,
  `sub_category_id` int(2) unsigned zerofill NOT NULL default
’00’,
  `manufacturer_id` int(3) unsigned zerofill NOT NULL default
‘000’,
  `name` varchar(35) NOT NULL default ”,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `category_id` (`sub_category_id`,`manufacturer_id`)
);

– holds sub category info: “AMD motherboards”, “Intel
motherboards”
CREATE TABLE `pc_sub_categories` (
  `id` int(3) unsigned zerofill NOT NULL auto_increment,
  `category_id` int(1) unsigned zerofill NOT NULL default ‘0’,
  `name` varchar(25) NOT NULL default ”,
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)
);

As you can see, each table contains a specific yet related type of information. This is referred to as “relational database design.” Each table contains a primary key field, which is used to uniquely identify each record found within. These primary key fields in turn are used to link each table’s records to records contained within other related tables. For example, “pc_categories” is used to contain major computer hardware categories such as “memory” and “motherboards.” Its primary key field is then used to establish a link to related records found within “pc_sub_categories,” such as “desktop memory” and “notebook memory,” or “AMD compatible motherboards” and “Intel compatible motherboards.”

The relationship between our four database tables is as follows:

Put another way, each hardware category contains one or more sub-categories, as well as one or more manufacturers. In turn, each computer part belongs to a sub-category and a manufacturer. In this way, all four tables in our database are related to each other despite the fact that they contain different types of data.

Another goal in proper relational database design is to reduce repetition in the storage of data. For instance, rather than storing the name of a manufacturer over and over again within our main “pc_parts” table, we simply store it once within the “manufacturers” table, and then refer to it using its primary key value. That way instead of storing a string of text we are simply storing a small integer value. This will result in a smaller overall database size — an important element to consider when dealing with large bodies of information.

If you would like a list of records to pre-populate your SQL database for testing purposes, you may obtain one here: http://portfolio.boynamedbri.com/devshed/search/part_1/data.sql. A link will also be provided to a finished version of the application we will be developing at the end of this article.

{mospagebreak title=Defining the Visual Layout}

The layout of the search form and the resulting records will vary widely depending on the surrounding website. However, for our example project we will stick with a simple layout that positions the search form at the top of the page, followed by a pagination menu (if more than one page of results have been found), followed lastly by the actual records returned for the search criteria given.

Our basic HTML layout, (“index.php”), will be as follows:

 <?php @ include_once ‘inc.core.php'; ?>

<html>
<head>
      <title>Inventory Search Page</title>
</head>
<body style=”background-color: #EEEEEE;”>

<div align=”center” id=”content_div”>
      <table>
            <form name=”search_form” action=”index.php”
method=”post”>             
            <tr>
                  <th style=”width: 175px;”>Category:</th>
                  <th style=”width: 175px;”>Sub Category:</th>
                  <th style=”width: 175px;”>Manufacturers:</th>
                  <th style=”width: 175px;”>Keywords:</th>
            </tr>
            <tr>
                  <td>
                        <!—- Categories Menu here –>
                  </td>
                  <td>
                        <!—- Sub Categories Menu here –>

                  </td>
                  <td>
                        <!—- Manufacturers Menu here –>
                  </td>
                  <td>
                        <!—- Keywords Menu here –>
                  </td>
                  <td>
                        <INPUT type=”submit” value=”go”/>
                  </td>
            </tr>
            </form>
      </table>
      <table>
            <tr id=”pagination_row”>
                  <td>
                        <!– Current Page here –>
                  </td>
                  <td>
                        <!– Pagination Menu here –>
                  </td>
            </tr>
      </table>
      <table>
            <tr>
                  <td id=”results_cell”>
                        <!—- Search Results here –>
                  </td>
            </tr>
      </table>     

</div>

</body>
</html>

As you can see, there’s nothing too complex about this layout, just a series of placeholders into which PHP code will later be inserted to display form fields, a pagination menu, search results, and so on. Again, our focus here isn’t on the layout but the concepts behind the search interface, so we’ll zip through the HTML design phase rather quickly.

Once our HTML is in place, we may also wish to add the following CSS to the document’s <HEAD> tag:

      <style type=”text/css”>
            body, p, th, td, select {
                  font-family: Tahoma, Arial, Verdana, Helvetica;
                  font-size: 11px;
            }
            table {
                  background-color: #FFFFFF;
            }
            input, select {
                  width: 90%;
            }
            #content_div {
                  width: 100%;
            }
            #content_div table {
                  width: 775px;
                  border: 2px solid #DDDDDD;
                  margin: 2px;
            }
            #content_div table th {
                  font-weight: bold;
                  text-align: left;
            }
             a {
                  font-weight: bold;
                  color: #000080;
                  text-decoration: none;
                  } 
                  a:hover {
                  text-decoration: underline;
            } 
            #results_cell table {
                  border: 0px hidden;
                  padding: 0px;
                  margin: 0px;
            }
            #results_cell .even {
                  background-color: #EEEEEE;
            }
            #results_cell .odd {
                  background-color: #DDDDDD;
            }
      </style>

Again, this is pretty basic. We’re just formatting the appearance of our page, and the results found within our page. We’re now done with the layout. All our page needs now is a little bit of JavaScript and we’re ready to dive into the PHP code:

      <SCRIPT type=”text/javascript”>
            function attach_file( p_script_url ) {
                  // create new script, set relative URL, and
load it
                  script = document.createElement( ‘script’ );
                  script.src = p_script_url;
                  document.getElementsByTagName( ‘head’ )
[0].appendChild( script );
            }
      </SCRIPT>

This technique may be unfamiliar to some, but it is merely a method of dynamically attaching a PHP file with JavaScript capabilities to our document at runtime. This is a topic thoroughly discussed in another recent article of mine, posted on Dev Articles, and I recommend you read it if you are interested in learning more.

Now we’re ready to start talking about the PHP framework that will pull this all together.

{mospagebreak title=How Do We Begin?}

There are a number of ways we could go about setting up our PHP code to handle the task at hand. However, since the ideas we are discussing are not limited to the particular application we are developing (the computer hardware storefront), it would be helpful to develop code that is as re-usable as possible. In order to do that, we will first need to take a moment to think about what exactly it is that we need PHP to do.

Our first focus is on the search form itself. For our example application, that form will comprise three SELECT menus and a text input field. Creating the text input field is simple, but our PHP will need to retrieve, sort, and display a proper set of options for each of the SELECT menus. To make things even more interesting, two of those menus (“Sub Categories” and “Manufacturers”) will be dependent upon values found within the first (“Categories”), a feature that will require additional JavaScript and PHP code to function.

As previously mentioned, our goal is to create code that is reusable, and a good first step for doing that is defining the proper code flow. Let’s go ahead and create the following files:

helper.php        // holds PHP & Javascript to dynamically update
menus
inc.conf.php      // holds application configuration variables
inc.core.php      // includes all necessary files
index.php         // main layout page (HTML & CSS)
search.php        // search helper class w/ various search
functions

As you can see, each file has a specific task/function. The “index.php” file we have already discussed, so let’s now take a look at the “inc.conf.php” page. At this point all we will need to do within our conf file is open a database connection:           

<?php
            # open MySQL connection
            mysql_connect( ‘localhost’, ‘root’, ” );
            mysql_select_db( ‘search_engine’ );
?>

This database connection will be used by PHP code found within other files (“helper.php” and “search.php” mainly), but instantiating it here helps keep things a little cleaner and easier to update, should our connection information change. Our “inc.core.php” file should also look pretty barebones at this point:

<?php
      # start session before any header information has been sent
      @ session_start();     

      # turn off error reporting on undefined indexes
      # NOTE: this was done to clean-up code and reduce ‘isset’
checks
      error_reporting( E_ALL ^ E_NOTICE );

      # include the core configuration page
      include_once ‘inc.conf.php';

      # include the Search helper class
      include_once ‘search.php';
?>

That’s it for our configuration and includes files for the time being, although we will come back to them with some enhancements as the article series continues.

Summing things Up

We’ve now put all of the pieces into place, and we’re ready to start assembling our search interface using the helper Search class we’ll be creating (“search.php”). So far our page looks pretty empty, but we’ll fill it up quickly once things get started. Your “index.php” file should currently display the following: http://portfolio.boynamedbri.com/devshed/search/part_1/

You’re now ready to move on to part two, and begin working on the Search class.

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