[Resolved] Need help with search & filter problem

dotty

Hi,

I have a search page which needs to search 5 fields so I've made 5 drop down menus. If I use "OR" - user can
only select one - using "AND" only works if a selection is made from all the menus. How do I handle the search so that if one or
more of the selections are not made that it will still bring back results for the other parameters? And that if a selection
is not made then the default will be that all the records in that field are searched?

I'm sure this is not difficult as I see it all the time on websites - but I just can't get my head around how best to handle this.

I also have another problem - in the SQL statement to make a recordset that shows results (SELECT id, name, company, etc. ) returns a pre-defined set of fields or (SELECT *) returns all fields - how can I get the results from a Multi-selection menu so that the user can bring up only the results they need? Something like (SELECT name_of_menu WHERE ...etc) - so is there some particular way that this should be defined?

Thank you in advance to anyone who can help me with this!😕

Pig

You need to build the $sql statement based on what they give you, instead of just throwing in variables.

For your second question, you need to handle that in the PHP side by choosing the results you want displayed. Never just print the entire array of results.

BuzzLY

You need to build the SQL on the fly, based on the information that is passed.

As an example, let's say you have a form where an HR manager can choose people from a database based on their title, department, and office location (in a very big company, of course).

If the user chooses a specific title (manager) and department (IT), but leaves office location to the option "choose an office," then you would build your SQL statement to read:

SELECT firstname, lastname, title, department 
   FROM users,departments 
   WHERE users.deptID = departments.ID 
      AND department = 'IT' 
      AND title = 'manager' 
   ORDER BY lastname

How you build the SQL is up to you, but it should be fairly straightforward in the PHP code. Then simply apply the SQL against your database, and you should get the right result set.

dotty

You need to build the $sql statement based on what they give you, instead of just throwing in variables.

The search using

SELECT * FROM dbname WHERE inputfield1 = dbfield1 AND inputfield2 = dbfield2

... etc ...for all dropdown menus - works well and returns the correct answer but only if a selection is made from each one. What I need is to allow the user NOT to select from all of the menus and that the ones where they don't select will search all the entries in that field and bring back a result based on the wider selection.

e.g. To select from all menus could be : Females from France aged 20-30 who like classical music and have opted in to the maillist

as opposed to: Females in All Europe aged 20-30 who like all music and have opted in to the maillist

How is the $sql for this written?

Apologies for my lack of php knowledge but I'm fairly new (but enthusiatic!!) to PHP

Thank You!

dotty

'BussLY '- I just saw your posting as I was replying to 'Pig'😃 so my response is not in order.

That sql looks like what I want - I'm off to try it out and will let you know how I got on.

Thank You 🙂

BuzzLY

Here's how SQL works...

In your statement:

SELECT * FROM dbname WHERE inputfield1 = dbfield1 AND inputfield2 = dbfield2

only the records where inputfield = dbfield1 and inputfield2 = dbfield2 will be returned. Both must be a match for a record to be returned in the recordset.

If you wanted the records where inputfield1 = dbfield1 and inputfield2 = anything (or everything), then simply omit it:

SELECT * FROM dbname WHERE inputfield1 = dbfield1

Now, inputfield2 doesn't matter.

The WHERE clause in SQL is restrictive -- the less restrictive you are, the more rows will be returned. If you think about that, it makes sense. Omitting the WHERE clause entirely will return every row:

SELECT * FROM dbname

Hope that helps!

BuzzLY

Originally posted by dotty
'BussLY '- I just saw your posting as I was replying to 'Pig'😃 so my response is not in order.

That sql looks like what I want - I'm off to try it out and will let you know how I got on.

Thank You 🙂

Gotcha. And it's BuzzLY, not BussLY... "Buss Lightyear to the rescue!" does not sound right... 😉

Good Luck.

dotty

Finally sorted out my problem. I used LIKE instead of = in recordset and made the first value ( Select All) in each selection menu a wildcard %. This returns the a result even if the user doesn't make a choice on all menus.

Thanks for all the help - great forum!