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.