Help with SQL for multiple search variables 1

[eaglesphan]

I have to create a search page with up to six search parameters. They are month, year, person type, event, department, and category. My table has one column each for the month, year, and event. It has three person type columns (person_1, person_2, and person_3), two for departments, and two for categories. There are more columns but they don't affect my query. Only the month and year cannot be null. All the rest can be null. I'm using PHP.

Now I know this query is going to be complicated as everything so I'm asking for help. My confusion is in trying to use the same variable for three different columns. The idea is when the initial recordset is added, any or none of the person, dept., categories, and event columns can have an integer value. This value is the foreign key to other tables that actually list the various names the intergers represent.

If all I had was one people_ column I could figure it out. But how does it work if someone searches for a person and that variable needs to be compared to all three people_X columns? Also I have to take into consideration that some values aren't going to be passed if the searcher elects not to search on that area. This is my WAG on how the WHERE clause would look:

WHERE month = 'month' AND year = 'year' AND people_1 = 'people' AND people_2 = 'people' AND people_3 = 'people' AND dept_1 = 'department' AND and so on.

Am I on the right track? And one last thing. If the searcher decides not to search on department, what do you list for the variable default if you want it to just skip that column? In other words, if the 'people' variable has no value, I don't want it to return all rows that have a null value in that column.

I hope this is making sense because after reading it, even I'm confused but I can't figure out how to make it any plainer.

 

[TonyGroves]

Since you don't know in advance which columns the user is going to be searching, you will have to make PHP assemble the query, including only the comparisions required.

For example:
(1) Start the query:[tt]
$query="SELECT * FROM tbl WHERE ";[/tt]
(2) If a month has been specified:[tt]
if($month)
{
$query.="month=$month AND ";
}[/tt]
(2) If a person has been specified:[tt]
if($person)
{
$query.=
"(people_1=$person OR people_2=$person"
." OR people_3=$person) AND ";
}[/tt]
and so on.
Finally, strip off the trailing "AND " from $query (I don't know how to do that; I've never used PHP!), and add any other SQL needed.

You can then send the query to MySQL for processing.



-----
ALTER world DROP injustice, ADD peace;

 

[eaglesphan]

Ahhhh! I see now and it makes more sense. I will now turn my attention to the PHP aspect of it and work on that this afternoon. Thanks a bunch.