I've got a table on a mysql server (4.1.10a-nt) that I want to query
against some search criteria. The table contains approx. 9, 000
records, and will eventually grow to hundreds of thousands.
Here are the fields in the table...
id int(unsigned) auto_increment
firstName varchar(30)
lastName varchar(30)
email varchar(75)
campus varchar(75)
year varchar(4)
When the user enters search criteria I want all records where there is
an identical match within any portion of the field.
i.e.
Searching for 'ave' returns all firstName's (dave), lastName's
(Shaverson), email's ([EMAIL PROTECTED]), etc...
Here's some php code to illustrate how I've implemented it...
if (strlen($searchCriteria) > 0)
{
$searchClause = " AND (";
$searchClause.= "firstName LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "lastName LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "email LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "CONCAT(firstName, ' ', lastName) LIKE
'%".$searchCriteria."%' OR ";
$searchClause.= "campus LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "year LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "email LIKE '%".$searchCriteria."%')";
}
Now let's say the user enters 'ave 2004'. In my applications current
state, no records are returned because none of the records have "ave
2004" anywhere. I'd prefer to have each word treated seperately, as well
as a full phrase. Such that only records that contain 'ave' somewhere
in any of the 5 varchar fields, *AND* '2004' somewhere in any of the 5
varchar fields are returned. For instance, all Dave's and Shaversons
who's year is 2004, or whose email address is [EMAIL PROTECTED] (for
example).
Full text searches will allow me to do this, however I'd prefer to be
able to query against even a single character, and I'm not sure what's
the most efficient method. Should I use something like
#Check against occurances of the full string first...
$searchClause = " AND (";
$searchClause.= "firstName LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "lastName LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "email LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "CONCAT(firstName, ' ', lastName) LIKE
'%".$searchCriteria."%' OR ";
$searchClause.= "campus LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "year LIKE '%".$searchCriteria."%' OR ";
$searchClause.= "email LIKE '%".$searchCriteria."%'";
//Now again for each individual word in the search criteria.
$criteriaArray = split(" ", $searchCriteria);
foreach ($cirteriaArray as $searchKeyword)
{
$searchClause.= "OR firstName LIKE '%".$searchKeyword."%' ";
$searchClause.= "OR lastName LIKE '%".$searchKeyword."%' ";
$searchClause.= "OR email LIKE '%".$searchKeyword."%' ";
$searchClause.= "OR CONCAT(firstName, ' ', lastName) LIKE
'%".$searchKeyword."%' ";
$searchClause.= "OR campus LIKE '%".$searchKeyword."%' ";
$searchClause.= "OR year LIKE '%".$searchKeyword."%' ";
$searchClause.= "OR email LIKE '%".$searchKeyword."%'";
}
$searchClause.= ")";
The results table is paginated, sortable, and pulled via Javascript
XmlHttpRequest. The search textbox is right above the table and the
results (number of pages, prev/next page buttons, total # records,
etc..) are all updated for every character added to the search text box.
With almost 10, 000 records, displaying 25 per page is *INCREDIBLY*
fast. And because it's all AJAX it needs to be.
My question is, should I use fulltext searches, or the above illustrated
method that'll generate one helluva long ass query string?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
