figuring out which field to look for a value in with SQL and perl

Question

I'm not too good with SQL and I know there's probably a much more efficient way to accomplish what I'm doing here, so any help would be much appreciated. Thanks in advance for your input!

I'm writing a short program for the local school high school. At this school, juniors and seniors who have driver's licenses and cars can opt to drive to school rather than ride the bus. Each driver is assigned exactly one space, and their DLN is used as the primary key of the driver's table. Makes, models, and colors of cars are stored in a separate cars table, related to the drivers table by the License plate number field.

My idea is to have a single search box on the main GUI of the program where the school secretary can type in who/what she's looking for and pull up a list of results. Thing is, she could be typing a license plate number, a car color, make, and model, someone driver's name, some student driver's DLN, or a space number. As the programmer, I don't know what exactly she's looking for, so a couple of options come to mind for me to build to be certain I check everywhere for a match:

1) preform a couple of

  SELECT * FROM [tablename] 

SQL statements, one per table and cram the results into arrays in my program, then search across the arrays one element at a time with regex, looking for a matched pattern similar to the search term, and if I find one, add the entire record that had a match in it to a results array to display on screen at the end of the search.

2) take whatever she's looking for into the program as a scaler and prepare multiple select statements around it, such as

  SELECT * FROM DRIVERS WHERE DLN = $Search_Variable
  SELECT * FROM DRIVERS WHERE First_Name = $Search_Variable
  SELECT * FROM CARS WHERE LICENSE = $Search_Variable

and so on for each attribute of each table, sticking the results into a results array to show on screen when the search is done.

Is there a cleaner way to go about this lookup without having to make her specify exactly what she's looking for? Possibly some kind of SQL statement I've never seen before?

Answer 1

Seems like a right application for the Sphinx full-text search engine. There's the Sphinx::Search module on CPAN which can be used as perl client for Sphinx.

Answer 2

First of all, you should not use SELECT * and you should definitely use bind values.

Second, the easiest way to figure out what the user is searching for is to ask the user. Have a set of checkboxes likes so:

Search among: [ ] Names 
              [ ] License Plate Numbers 
              [ ] Driver's License Numbers

Alternatively, you can note that names do not contain any digits and I have not seen any driver's license number which contains digits. There are other heuristics you can apply to partially deduce what the user was trying to search.

If you do an OK job of presenting the results, this might work out.

Finally, try to figure out what search possibilities are offered by the database you are using and leverage them so that most of the searching happens before the user interface touches the data.