Search all except...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Search all except...

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post 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??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the sql is a simple string, so yes, you are able to do that.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post 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'
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post 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!!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you didn't write a bit for when $_GET['sing1'] isn't set. (that won't generate notices)
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

except not passing the variable.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

You mean if the page is accessed directly without going first thru the search form?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes.. or other subterfuge, like submitting only certain fields.
WLW
Forum Commoner
Posts: 41
Joined: Sat Aug 07, 2004 3:00 pm

Post by WLW »

Do you mean, change:

$my_sing = ""; //set default

to:

$my_sing = "%"; //set default
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I mean, there's no need to search on the field, if it wasn't submitted.
Post Reply