search for person by comparing his data with data in mysql

[Afan Pasalic <afan@xxxxxxxx>]

hi,
I have to build a little "search" form.
a visitor enters his/her personal and work data (first name, last name, 
email, org. name, phones (home phone, work phone, cell) home address, 
work address) using a form, and then administrator has to compare these 
data with existing data in database (mysql). if record in database has 
the same first name as the visitor, the record will get 1 point. if the 
last name is the same - 3 points. if both names are the same - clearly 4 
points. if email matches 7 points, phone number 4 points, etc.

the list to be shown on the screen is list of all records they have at 
least 1 point. and the list has to be sorted by number of points.

also, matching parts on the list have to be highlighted (with different 
background color, of the font different color).

I did some testing and the code is really basic, using LIKE, and I'm 
assuming not so good way. first, I'll get all records from database. 
while "reading" I compare data from DB with visitor's data. my query 
(simplified) looks something like this



$query = "
       SELECT p.person_id, p.first_name, p.last_name, p.phone as 
phone_home, p.primary_org, p.address_id, p.email as personal_email, 
o.full_name, o.organization_id, o.phone as phone_work, o.address_id as 
org_address, a.address1, a.city, a.state, a.zip, a.county
       FROM people p
       LEFT JOIN organization o ON 
(o.instance=".$_SESSION['instance']." AND 
o.organization_id=p.organization_id)
       LEFT JOIN addresses a ON (a.entity_id=o.organization_id AND 
a.instance=".$_SESSION['instance'].")
       WHERE p.instance_id=".$_SESSION['instance']."
           AND m.instance_id=".$_SESSION['instance']."
           AND p.person_id = ".$_SESSION['person_id']."
           AND o.active='Y'
           AND (
                       p.last_name LIKE 
'%".mysql_real_escape_string($person['last_name'])."%' OR
                       o.email_address = 
'".mysql_real_escape_string($person['email_address'])."' OR
                       p.email = 
'".mysql_real_escape_string($person['email'])."'
                  ";
if (!empty($phone_home))
{
   $query .= "
               OR p.phone = '".$person['phone']."'";
}
if (!empty($person['phone']))
{
   $query .= "
               OR o.phone = '".$person['phone']."'";
}
if (!empty($person['org_name']))
{
   $query .= "
               OR o.full_name LIKE 
'%".mysql_real_escape_string($person['org_name'])."%'";
}

$query .= "
               )
       ORDER BY p.last_name ASC, p.first_name ASC ";


$myquery = mysql_query($query);
while($result = mysql_fetch_array($myquery))
{
   # I compare record with visitor's data and assign points to 
$RANK[$result['person_id']]
   # if there is at least one "match"  assign the record to an array 
$RECORDS['person_id']
}

then sort $RANK desc and then list sorted array $RANK on screen with 
matching $RECORDS elements.

It works but it could take 10-15 seconds to create the list if database 
has e.g. 10,000 records


anybody had the same or similar project? I'll appreciate any suggestion. 
how to setup database (mysql) and the best way to do the search code (php).

thanks for any help.



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php