Search function 2

[sipps]

Hi all,

I have created a search page which looks at three recordsets, and checks their attributes against what the user has input in a text box.

One of the searches checks against a customers first and last name, so if I typed in 'Joe' ot 'Bloggs' then it would find the customer 'Joe Bloggs', but I can't figure out how to search for he customer Joe Bloggs when both his first and last name are input into the text box.

My search query is simple:
SELECT Customer_ID, Customer_Fname, Customer_Lname, Customer_Postcode, Customer_Email
FROM customer
WHERE Customer_Fname LIKE '$HTTP_POST_VARS[searchtext]' OR Customer_Lname LIKE '$HTTP_POST_VARS[searchtext]' OR Customer_Email LIKE '$HTTP_POST_VARS[searchtext]' OR Customer_Postcode LIKE '$HTTP_POST_VARS[searchtext]'

Is there something I can do to get it to send back a result if it is in the Customer_Fname or Customer_Lname?

Also, when I use the % sign in the query, it shows all teh records when the user first gets to the search page, when no search criteria has been entered, is there anyway of stopping this from happening apart from jumping to a new page to show the results?

Thank you,

sipps

 

[sleipnir214]

Split the input at the space, then assume the first word should be looked up in the fname column, and the second and subsequent words should be looked up in the lname column.

Or you could split the input at the spaces, then construct a much more complicated query which looks something like:

$search_array = split (" ", $HTTP_POST_VARA['searchtext']);

$where_clause = "WHERE";
$precede_or = FALSE;
foreach ($search_array as $word)
{
if ($precede_or)
{
$where_clause .= " OR ";
}
$precede_or = TRUE;

$where_clause .= " Customer_Fname LIKE '%" . $word . "' OR Customer_Lname LIKE '%" . $word . "' OR Customer_Email LIKE '%" . $word . "' OR Customer_Postcode LIKE '%" . $word . "'";
}


To stop the spurious report, what I would do is surround your lookup code with an if statement. If your search clause input field is named "foo", then when I got to the part which poplulates the search results return field I would:

if (array_key_exists('foo', $_POST))
{
//then perform the search and output search results
}
else
{
//only output the HTML necessary to make the results section blank
}
Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Thank you!!