Page 1 of 1
Search all except...
Posted: Mon Sep 06, 2004 12:15 pm
by WLW
I have a database with a newly added column called SINGER. The column can contain any of a dozen or so items (alto, soprano, etc.) selected from a dropdown list. One of those items is n/a, meaning that this person is NOT a singer.
On the search form I want to add an option to search for singers by type (alto, soprano, etc.) or to show "all singers" (exclude the records containing"n/a" in that column). It also must allow a "no choices selected" default which doesn't exclude "n/a".
I know I could setup the MySQL query to include a "WHERE SINGER <> 'n/a'", but that would never allow the records with n/a in that column to be displayed in the "no choices selected" default search.
I assume I will need to generate something dynamically that can be added to the existing WHERE statement already setup for the other search parameters.
Any ideas?
Posted: Mon Sep 06, 2004 1:14 pm
by feyd
yep, you'll need to dynamically alter the search string you use.. Just analyze value of the drop down for the data that matters: whether they have a type selected or not.
Posted: Mon Sep 06, 2004 1:44 pm
by WLW
I think WHERE SINGER LIKE 'sing1%' can work to allow either a specific singer choice (if 'sing1' is 'Alto' or 'Soprano', etc.) OR the default of show all (if 'sing1' is %), but when "All Singers" is selected, what value gets passed as 'sing1' in order to exclude 'n/a' from the search?
The dropdown on the form would have:
All Singers (what value gets passed??)
Alto
Soprano
Mezzo-Soprano
.
.
.
Can a script generate something that means:
If 'sing1' = "ALL" then append with SINGER <> 'n/a', otherwise append with SINGER LIKE 'sing1'
...and would that solve my problem??
Posted: Mon Sep 06, 2004 3:11 pm
by feyd
the sql is a simple string, so yes, you are able to do that.
Posted: Mon Sep 06, 2004 3:36 pm
by WLW
Unfortunately, I am not very familiar with the PHP coding necessary to create the dynamic statement that means:
If 'sing1' is equal to "ALL" then append the SQL WHERE statement with SINGER != 'n/a', otherwise append with SINGER LIKE 'sing1%'
Can you help me with that scripting?
I think the script concept above needs to generate:
1) show all 10 [SINGER LIKE 'sing1%'], form does not pass anything for 'sing1', the default value % gets used instead.
2) show any specific record(s) [SINGER LIKE 'sing1%'], form passes a specific singer type as 'sing1'
3) show the 5 records that are NOT "n/a". [SINGER != 'sing1'], form passes n/a as 'sing1'
Posted: Tue Sep 07, 2004 11:14 pm
by WLW
I found another approach that works perfectly:
Code: Select all
//array of all possible VALID singer types
$sing_type = array("%","n/a","Soprano","Mezzo-Soprano","Alto","Tenor","Baritone","Bass");
$my_sing = ""; //set default
//check to see if the form has a value and that value is in the array
if ((isset($_GET['sing1']))&&(in_array($_GET['sing1'],$sing_type))){
$my_sing = $_GET['sing1'];
}
//check the value of the eye color and include it in the sql if a valid color is choosen
if ($my_sing != "n/a"){
$query_rs_members .= " AND (members.singer LIKE '$my_sing%')";
} elseif ($my_sing = "n/a"){
$query_rs_members .= " AND (members.singer != '$my_sing')";
}
Thanks for the ideas!!
Posted: Tue Sep 07, 2004 11:20 pm
by feyd
you didn't write a bit for when $_GET['sing1'] isn't set. (that won't generate notices)
Posted: Tue Sep 07, 2004 11:35 pm
by WLW
The form dropdown list is:
Code: Select all
<select name="sing1" id="sing1">
<option value="%" selected>--</option>
<option value="n/a">All Singers</option>
<option value="Soprano">Soprano</option>
<option value="Mezzo-Soprano">Mezzo-Soprano</option>
<option value="Alto">Alto</option>
<option value="Tenor">Tenor</option>
<option value="Baritone">Baritone</option>
<option value="Bass">Bass</option>
</select>
I tested it using an echo on the final SQL, and the script seems to generate exactly what I wanted for all form options with no problems...
Posted: Tue Sep 07, 2004 11:37 pm
by feyd
except not passing the variable.
Posted: Tue Sep 07, 2004 11:45 pm
by WLW
You mean if the page is accessed directly without going first thru the search form?
Posted: Tue Sep 07, 2004 11:50 pm
by feyd
yes.. or other subterfuge, like submitting only certain fields.
Posted: Wed Sep 08, 2004 6:17 am
by WLW
Do you mean, change:
$my_sing = ""; //set default
to:
$my_sing = "%"; //set default
Posted: Wed Sep 08, 2004 12:21 pm
by feyd
I mean, there's no need to search on the field, if it wasn't submitted.