In this article, you'll learn how to create a searchable form feature that will query a database table and display current staff member information. During the analysis you'll learn how to do the following: create a database table that will hold current staff listings, create a search form and use PHP, in coordination with Structured Query Language (SQL) to capture information entered by the visitor and append the information to display the results we want to show. Additionally, you'll learn about design patterns with the searchable form including security through code to ensure that only certain input is entered before performing operations on your database table and ways to pass state information through the address bar to display additional information about staff members.
What You'll Need
In order to complete this article, you'll need the following tools and technologies:
- One My SQL database
- PHP support
- A text editor
Creating Our Database
If you're unsure how to create a database through your host, please contact your system administrator for further information. Once you've created a database, you'll need a way to connect to it, create a table, and enter data for appropriate entities. One popular database administration tool for My SQL is PHP My Admin, or one that I'm particular fond of is SQLyog, which currently supports a free version (Community Edition) which is sufficient for the purposes of this article.
Creating Our Table
Our table needs to be created with the following columns and their corresponding data types and lengths:
|Column Name||Data Type||Length||Null or Not Null||Primary key?||Auto Increment|
While an in-depth analysis of database design is beyond the scope of this article, let's briefly discuss the concepts of columns, data types and lengths. A database table is composed of columns and rows, much like an Excel spread sheet. A column gives us a unique way to identify a certain entity, such as a first name. Data types are merely what type of data the column stores, such as a number, variable characters, date, time, etc. Lengths allocate a specified amount of memory (storage) to the table column. In our case, we're using variable characters that allow more flexibility. In other words, if the first or last name doesn't occupy exactly 50 units of data in our column, only that which is occupied will be allocated and stored. Furthermore, there should be no null (empty) entities for any staff members. Lastly, it should be noted that the reason our first row is highlighted in yellow is to identify that the ID column is our primary key. A primary key in database design ensures that each record is guaranteed to be unique. This column is also set to auto increment, ensuring that each record has a unique number assigned to it by the database engine.
Enter Staff Members into the table
Once you've created the columns for your database table, proceed to fill your columns with data. Six records should suffice for the purposes of this article. A replica of mine is below:
Create the Form
In order to create the search form, you'll need to open a text editor of your choice. One that is free is PSPad. You can use any text editor you want; though it's recommended to choose one that has syntax highlighting to make PHP debugging and programming a bit easier. When creating the search form page, make sure to save it with a
.php extension, otherwise, the PHP code will not execute. Once the form is saved, begin with the following markup:
If you're familiar with HTML, everything should look familiar until you reach the opening form tag. Inside the opening form tag, the key line of code is the action attribute. We set the action of the form to the name of our file and then append on a query string of “go”. We'll discuss why this is important in the next section (