Help on how to get the right stuff from MySQL

[brad62]

I have a website with a couple of coverbands.
They update their own info about their band and one field is called style.
The can choose what kind of style they play (jazz, soul, pop, rock, and so on).
Some of them pick more than one alternative and in mySql the choices are marked as 1 or 0.

Now I'm building a page where people can search for a specific band.
They can search for bands by price, origin and style.

So I have used SELECT * from table_bands WHERE place LIKE $place AND price LIKE $price AND pop LIKE $pop AND jaz LIKE $jazz and so on...

People who search have a dropdown where they select the laternatives and the can choose different styles or all styles (which generates a %%).

So I made something like

if ($style=='jazz') {
$jazz='1';
$pop='0';
$soul='0';
$rock='0';
$other='0';
}
if ($style =='pop') {
$jazz='0';
$pop='1';
$soul='0';
$rock='0';
$other='0';
} 

This works fine if you're looking for, let's say, a band that plays jazz.
But the problem is if you look for a jazz band and a band have marked both jazz and soul. Then I've set the search to include only those who have marked only jazz as 1.

I just can't figure out how to do this properly without changing all the fields in my db. Can anyone give me a clue on what to do here?
My brain is not working like it should now. I'm just blocked here...

[mudshark]

Something like this perhaps:

<?php
if(!empty($_POST)){
    $sql = "SELECT * FROM bands WHERE ";
    foreach($_POST['type'] as $k => $v){
        if($v != 0){
            $sql .= "type = " . $v . " OR ";
        }
    }
    echo rtrim($sql, " OR ");
}
?>
<form method="post" action="">
    <select name="type[]">
        <option value="0" selected="selected">Choose...</option>
        <option value="1">jazz</option>
        <option value="2">pop</option>
        <option value="3">rock</option>
    </select>
    <select name="type[]">
        <option value="0" selected="selected">Choose...</option>
        <option value="1">jazz</option>
        <option value="2">pop</option>
        <option value="3">rock</option>
    </select>
    <select name="type[]">
        <option value="0" selected="selected">Choose...</option>
        <option value="1">jazz</option>
        <option value="2">pop</option>
        <option value="3">rock</option>
    </select>
    <input type="submit" value="go" />
</form>

(barebones, but you get the idea)

[brad62]

Didn't understand that. I'm a newbie almost...

[mudshark]

Well I could try 'n explain what it does, but I think you'll understand better if you just copy/paste and execute the above. I'm only echo'ing out the $sql query string there, but obviously, you'd use $sql to query the database.
Post some code if you'd like to know how to incorporate it.

[fredanthony]

Also, you might want to use a switch instead:

<?php

switch ($style) {
    case "jazz":
         $jazz = 1;
             $pop = 0;
             $soul = 0;
             $rock = 0;
             $other = 0;
    break;    

        case "pop":
             $jazz = 0;
             $pop = 1;
             $soul = 0;
             $rock = 0;
             $other =0; 
        break;

       }  

?>

[brad62]

But what fredanthony wrote. What will happen if a band have both jazz and soul?
Then if a viewer searching for jazz won't see that band, since their field with soul will be looking for one with a zero in that field or am I wrong?

[foofoonet]

I think its called Funk (or maybe that's Phunk?)

[brad62]

I just came up with a solution this morning that I hope will work.
I use it in other fields where it's a string. I people pick all choices then I make it %% (which is used as a wild card).
So if I set all styles to wild cards and only change the one they pick to 1, then it will find the right ones.
But "%%" didn't work as a wild card here. Maybe that's because the field is set as a mediumint? Is there another sign to use as a wild card then?

[brad62]

Sorry, it worked. I just forgot to put ' around the $style.
So, finally my brain started working. But guess this isn't the best solution but I think it will work. Now I will try it with all kinds of combinations.

[brad62]

Nope it din't work as I wanted. But very close.

If they pick a style, then it works great. But if they pick the wildcard to list all styles, then the query won't work.

I have written

jaz='$jaz' 

which is working fine for picking a style but it won't work with a wildcard. Then it has to be

jaz LIKE '$jaz' 

But how do I get both of them to work? If the choose ALL from the dropdown, then I'm posting %%. If the choose jazz then I post 'jaz'.