Search select statement

Question

I am creating a page which would have different field for the user to search from. e.g. search by:

• Grade: -dropdownlist1-
• Student name: -dropdownlist2-
• Student ID: -dropdownlist3-
• Lessons: -dropdownlist4-
• Year: -dropdownlist5-

How do I write the select statement for this? Each dropdownlist would need a select statement which would extract out different data from the database.

But, I want to write ONE select statement which can dynamically choose the dropdownlist options. Instead of writing many many select statement.

Lets say;

• Grade: -dropdownlist1- ; default value(all)
• Student name: -dropdownlist2-; default value(all)
• Student ID: -dropdownlist3-; 0-100 is choosen
• Lessons: -dropdownlist4-; A-C is choosen
• Year: -dropdownlist5-; 2009 is choosen

Answer 1

It depends on the language you're using between SQL and the page. But test the dropdown for a non-generic value and then add in a where clause:

sql = "select * from people where 1 = 1";
if(dropdownlist.value != "All")
  sql += "and name like '%" + dropdownlist.value + "%' ";

(watch out for SQL Injection though)

Answer 2

Extending to Tom's answer,

Create a view first which will contain the needed record set. Query on the view.