search for person by comparing his data with data in mysql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux