But what if the user does not want to submit a precise query but needs a list of the possibilities? There are a couple of ways to clarify the search. We could first keep a list of the common search queries. This is something done often by the likes of Google and Yahoo!. This works very well with large datasets served through web servers because it uses a static list of terms and simply culls them out. For more dedicated applications, one can use MySQL's pattern matching ability to present known options on-the-fly.
Where Python's regular expression engine is very robust, MySQL supports the two following metacharacters for forming regular expressions:
%: Zero or more characters matched in aggregate_: Any single character matched individually
Pattern matching is always a matter of comparison. Therefore, with either of these, never use operators of equality.
SELECT * FROM menu WHERE name = 's%'; WRONG SELECT * FROM menu WHERE name <> 's%'; WRONG
Instead, use the keywords LIKE and NOT LIKE.
SELECT * FROM menu WHERE name LIKE 's%'; RIGHT SELECT * FROM menu WHERE name NOT LIKE 's%'; RIGHT
Using metacharacters, one can match records using very irregular terms. Some of the possible combinations follow below:
s%: A value that begins with the letter s%s: A value that ends with the letter s%s%: A value that contains the letter ss%l: A value that begins with s and ends with ls%l%: A value that begins with s and contains at least one instance of the letter ls_l%: A value that begins with s and whose third letter is l_____: A five letter value (that is five underscore characters in succession)__%: A value with at least two characters
For a smaller dataset or even larger datasets served over low-contest or no-contest connections (for example local servers or dedicated LAN connections), there is the option of running a live query to present the user with the possible options. If the user has specified the database and table to be used, as in the example seen previously, then it is a small matter to match patterns in a column using LIKE and a regular expression.
The MySQL sentence for what we are doing, along with its results, is as follows:
mysql> SELECT name FROM menu WHERE name LIKE 's%'; +--------+ | name | +--------+ | salmon | | sole | +--------+ 2 rows in set (0.00 sec)
Tip
It is important to phrase the query in such a way as to narrow the returned values as much as possible.
Here, instead of returning whole records, we tell MySQL to return only the namecolumn. This natural reduction in the data reduces processing time for both MySQL and Python. This saving is then passed on to your server in the form of more sessions able to be run at one time.
In Python, the preceding statement would look like this:
column = 'name' term = 's%' statement = """select %s from menu where name like '%s'""" %(column, term)
Using the conversion specifier (%s), this code can easily be adapted for more dynamic uses.
Having restricted the parameters of the search, we are in greater control of the results and can therefore anticipate the number of fields in each record returned. We then have to execute the query and tell the cursor to fetch all of the records. To process the records, we iterate over them using a pattern similar to what we used previously:
command = cur.execute(statement)
results = cur.fetchall()
column_list = []
for record in results:
column_list.append(record[0])
print "Did you mean:"
for i in xrange(0, len(column_list)):
print "%s. %s" %(i+1, column_list[i])
option = raw_input ('Number:')
intoption = int(option)
The results for this code are:
Did you mean: 1. salmon 2. sole Number:
Naturally, we must then test the user input. After that, we can process the query and return the results.
This example is shown using terminal options so we do not use any JavaScript to transfer the options. However, in modern day reality, any application that relies on a web browser—either for background processing or for a primary interface, can use this code with minor modifications.