What's the best way to make a multi variable search

[aeroswat]

So this is what I want to do and I'm asking for ideas on the easiest way to go about doing this.

I have a database of orders that store many variables per order. I want to make a search form that is able to search one variable or many variables. This is what I have so far...

 

1)Create a representation of the whole order form in an html form

2)Allow the user to enter whichever information they want to

3)Have them press submit and it show the information for the first order that it pulls up matching that information

4)On the same page have links to next record in search (if not on the last), previous record in search (if not the first), and links for each individual record (kinda like googles search)

 

This is what I was thinking of doing but it seems sort of tedious for the machine to have to query the database every single time and i'm not sure if there's also a better way of storing the query string.

 

1) Have the php create the long query string by reading each text field and inserting a WHERE clause for each.

2) Store this in a hidden input field for the first record and in the URL for entries after that

        OR

2) Store the returned order numbers seperated by a delimeter in the URL and check with math which record to show next or previously based on the order number of the current one.

 

I could use a session variable too and store the array but that would get messy... wouldn't it?

[Psycho]

I'm not following your proposals. But what you are asking for is simply a pagination with one record per page. Just create your query and use LIMIT based upon the record you want to view. Also, if you want to allow multiple serach terms for the same field you would just use IN instead of an equals (i.e. =)

 

SELECT *
FROM orders
WHERE itemName IN ('product1', 'product2', 'preoduct3')
ORDER BY orderNumber
LIMIT 0, 1

Change the 0 based upon what page you are on (i.e. page-1).

 

Store the query parameters in session variables.

[laffin]

go with option 1). but use an array as form variables

 

<form action="form_action.asp" method="get">
  First name: <input type="text" name="form[fname]" /><br />
  Last name: <input type="text" name="form[lname]" /><br />
  <input type="submit" value="Submit" />
</form>

 

on your processing script create a loop to process form array, then create the sql.

foreach($_GET['form'] as $key=>$val)
{
   if(strlen(trim($val))
   {
        $form[]="{$key}='". mysql_real_escape_string(trim($val)) .'\'';
   }
}
if(!count($form))
   $where='';
else
$where=' WHERE '. implode(' AND ',$form);

 

I think that should do it

 

as you said, store the sql in a session variable (it would be bad if users were able to edit the sql statement) as well as the offset.

than ya can query the records as you go along

 

Edit: After rereading your post, the 2nd method is better.

using serialize,unserialize to store the query result set from the database, so all records ids are in an array, into a session variable or cookie (if using cookies, remember to check the ids, or use intval on them).

it should provide a performance boost, as well. since sql just has to locate the record id, instead of a series of WHERE clauses.

 

 

[aeroswat]

Thanks guys! I think i will go with method 2-2