Creating a Searchable Database

      One of the most useful applications of Perl is its ability to search through structured files for information, and use that information in generating a report. What I am talking about is the famous flat file database. In the simplest scenario, we may have a few fields to store information. The same format is then used for many different entries.

      The information is structured in such a way that it resembles a spreadsheet. One line (or row) is an entry in the database. Each field is separated by a delimiter character. This can be a tab, a comma, a pipe (|), or any other unique character. I prefer to use a pipe because it makes things more visibly separated.

      Lets take an address book entry as an example: 

      Last Name|First Name|Address|City|State|ZIP|Phone Number|Other

      OK, so an example entry would be: 

      Doe|John|123 Flat Road|Raleigh|NC|27695|(999) 999-999|Jane's cousin
      
Doe|Jane|321 Round Road|Hendersonville|NC|28739|(888) 088-8888|John's sister
      Anything that is between the ||'s is a field.

      Perl can easily extract the information in the database by using the split function. This takes a string and splits it using any character, into an array--Perfect! 

      open(DB,"database");
while ($entry=<DB>){
	@fields=split(/\|/,$entry);     #escape the pipe character \|
	
	print "$fields[0],$fields[1],$fields[2],$fields[3]";
}
close DB;

      The while loop will go through every entry in the database. For each entry, the fields are split up into an array. You can then access each field by number. Remember that arrays start with 0, so the first field is $fields[0]. You can do anything you want with the data once you have extracted it. You can print out a nicly formatted HTML table with the data in it.

      How to search the database

      Now suppose you put a restriction on the above code segment: You can only print the entries that pass a test. This test can be wheter the entry contains a particular word, or if a certain field matches a word. The test is up to you, and it determines the functionality of your search.

      Lets say we want to find all of the people in our address book whose last names match a search term. We would insert this if statement after the split line: 

      if ($fields[0] eq $in{"lastname"}){
	#Print the entry
}

      "lastname" would be the name of a form element on the HTML page calling this script, and it contains the string we want to search for. If it equals the entry in the database, (case sensitive, letter by letter) the entry is printed.

      We could make the search be more specific by adding more ifs: 

      if ($fields[0] eq $in{"lastname"}){
	if ($fields[1] eq $in{"firstname"}){
		#Print the entry
	}
}

      Now, how would you add information to this database?

      You can create a separate form and script that will allow a user to enter all of the information in each field and then append to the database. 

      <FORM METHOD=POST ACTION=/cgi-bin/addentry.pl>
<INPUT NAME=firstname><BR>
<INPUT NAME=lastname><BR>
<INPUT NAME=address><BR>
<INPUT NAME=city><BR>
<INPUT NAME=state><BR>
<INPUT NAME=zip><BR>
<INPUT NAME=phone><BR>
<INPUT NAME=other><BR>
<INPUT TYPE=SUBMIT></FORM>


      

#!/usr/bin/perl

require "cgi-lib.pl";
&ReadParse;

open(DB,">>database");

print DB "$in{"firstname"}|";
print DB "$in{"lastname"}|";
print DB "$in{"address"}|";
print DB "$in{"city"}|";
print DB "$in{"state"}|";
print DB "$in{"zip"}|";
print DB "$in{"phone"}|";
print DB "$in{"other"}\n";

close DB;

      We simply print out the information from the form, a separator character, and end the entry with a \n.

      Contents