Where value='all' ?

[EvilAsh]

Hi all.

I am struggling with a query. Please can you help?

Variables are passed to a query from select options in a html form, ie:

select * from table where column1 ='variable1' and column2 ='variable2' etc

Now this works fine, but I want to add an option that returns all the values from a given column, eg:

select * from table where column1 ='All Values' and column2 ='variable2' etc

I hope I have explained this ok. Can anyone advise me?

Thanks.

 

[TonyGroves]

Do you mean you want to restrict the results to a particular column? You can do this with:
[tt]
SELECT colname FROM tblname WHERE ...
[/tt]

 

[r937]

the way to do this is to use your application scripting language (asp? php? coldfusion?) to detect when the html form field has been selected with the "all" option, and then do not generate a WHERE condition for this column

this application logic is made much simpler if you start the WHERE clause with the condition 1=1

example --

select col1, col2, ...
  from daTable
 where 1=1

[COLOR=red]if formfield1 <> "all"[/color]
   and col1 = '$formfield1'
[COLOR=red]endif[/color]

[COLOR=red]if formfield2 <> "all"[/color]
   and col2 = '$formfield2'
[COLOR=red]endif[/color]

r937.com | rudy.ca