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
