Select where.....

xserban

I have a dropdown <select> list and a button. If I press the button, it will select from a table infos for the value selected from the dropdown list.
I need a way to check in my select query what was the value for the button.
Ex: the dropdown contains
0 <-this is for NULL
1
2
3
All

I need a way to do something like this: 😃 not actually pure SQL 😃

select * from table where field=if(dropdown=0 return null)
if(dropdown=All return 1, 2, 3, 4)
I hope you understand.
I need to do this in SELECT because i will have plenty of dropdowns in my form.
I was thinking also to check the variables in PHP, build the actual query string before the select query, something like this....

$string='';
if (dropdown>=1)&&(dropdown<=4) $string=$string.'AND field=$dropdown';
else $string=$string.'AND field>=1 and Field<=4';
.....
.....
and then the query.
This would work but I am convinced that this is not the best way.

sarahk

howzabout

<script language="JavaScript">
function getVal( fldname)
{
    var f = document.myform;
    var selectBox = f[fldname];
    var ind = selectBox.selectedIndex;
    var fldNode = selectBox.options[ind].firstChild;
    var fldValue = f[fldname+"desc"];
    if (fldNode != null)
    { 
      fldValue = fldNode.nodeValue;
    }
}
</script>
<select name="drop1" onchange="getVal('drop1');"><option>...
<input type="hidden" name="drop1desc" value="">

So you use javascript to populate the "desc" hidden fields and then your php can just use them. If you send the page back to the user (maybe required fields aren't entered) you will need to ensure that your php sets up the default values for the select and inputs correctly. A bit of work to set up but once you have the hang of it you will get alot of quick and easy reuse.

tomhath

If I understand it, you're select box gives three general types of choice:

0 - return where field is null
ALL - return every not null value
1..N - return the specific value

So you query should look something like this (simplified for clarity):

select * from table
where dropdown = 0 and field is null)
or (dropdown = 'ALL' and field is not null)
or (dropdown = field)

You'll have to put $dropdown in quotes since it's treated as a string and deal with field being a number. After php evaluates it, the query will look funny but should work:

select * from table
where ('0' = '0' and field is null)
or ('ALL' = 'ALL' and field is not null)
or ('1' = convert(varchar,field) )

tomhath

Too early in the morning for me to be posting. I hope you understood that even though I messed it up. Essentially the query would end up looking like one of these after all substitutions, depending on whether they picked 0, ALL, or a number:

---- selected 0 ----
select * from table
where ('0' = '0' and field is null)
or ('0' = 'ALL' and field is not null)
or ('0' = convert(varchar,field) )

---- selected ALL ----
select * from table
where ('ALL' = '0' and field is null)
or ('ALL' = 'ALL' and field is not null)
or ('ALL' = convert(varchar,field) )

---- selected 1 ----
select * from table
where ('1' = '0' and field is null)
or ('1' = 'ALL' and field is not null)
or ('1' = convert(varchar,field) )