[RESOLVED] searching a field for multiple values

nc_brox

Hi guys, hope someone can advise me on this, I can't seem to work out how to do this.

Basically I've got a mysql table, where one of the fields is 'Sports', and another is username. This could contain either one sport, or a number of sports (separated by a comma).

I'm trying to create a search form where users can find records depending on the sport they select. I.e. so if they choose Tennis, then it will bring up all the usernames that have the value tennis in the Sport field. I also need it to bring up all the usernames that have for example tennis, yoga and aerobics - as this would still be a match (this is one of the main problems that I am having at the moment).

Also, the user can select multiple sports to search on. So for example they can select tennis and badminton to search, so if a record has tennis is would be found, if a record has badminton it would be found, and if a record had tennis or badminton plus some other sports, it would be found aswell.

I know it's something to do with arrays but I'm not all that great at getting my head around arrays so any help would be much appreciated!

:queasy:

Piranha

You should look into database normalization. Then you will see that the design of the database is wrong. A good guide is at phpbuilder.com under articles.

nunomira

hi,

What are you exactly having problems with? With the queries?

SELECT username from myTable WHERE sports LIKE '%tennis%'

SELECT username from myTable WHERE sports LIKE '%tennis%yoga%aerobics%'

SELECT username from myTable WHERE sports
sports LIKE '%tennis%'
OR sports LIKE '%yoga%'
OR sports LIKE '%aerobics%'

Notice that storing all these sport names, repetitively, for each of the users, is not a good practice. You should have at least an extra table with the sports' names and an ID, and use these IDs in the table you already have or in another table that holds the relationship between the users and the sports.

nc_brox

hi, no it's not the queries that I'm having trouble with it's getting it to loop through so that it checks each sport and on each user.

As my variable is an array then in the query I would just put '$value' instead of the sport name (I've checked that if I print $value when more than one sport is selected then all the sports appear), but value could actually be tennis AND yoga AND badminton, and I can't do my query as:

SELECT username from myTable WHERE sports
sports LIKE '%$value%'
OR sports LIKE '%$value%'
OR sports LIKE '%$value%'

as this wouldn't work.....

I'm not sure how to get it to check for each different '$value'.

Piranha

nc_brox wrote:
hi, no it's not the queries that I'm having trouble with it's getting it to loop through so that it checks each sport and on each user.

As my variable is an array then in the query I would just put '$value' instead of the sport name (I've checked that if I print $value when more than one sport is selected then all the sports appear), but value could actually be tennis AND yoga AND badminton, and I can't do my query as:
SELECT username from myTable WHERE sports
sports LIKE '%$value%'
OR sports LIKE '%$value%'
OR sports LIKE '%$value%'
as this wouldn't work.....

I'm not sure how to get it to check for each different '$value'.

With a normalized database it would not be a problem. And it would solve a lot of other problems as well.

nc_brox

thanks - I'm just having a look at the article now...

nunomira

Yes, the normalized table solves the problem, as it doesn't make sense thinking about using AND when you have one field only.

In this case (really bad practice) you'd have to use the query

SELECT username from myTable WHERE sports LIKE '%tennis%yoga%aerobics%'

which means that you have to build the string '%tennis%yoga%aerobics%' by looping through the array.

Just for the example's sake:

$query = "SELECT username from myTable WHERE sports LIKE ";
foreach ($arr as $val)
{
	$query .= "'%" . $val . "'";
}
$query .= '&';

nc_brox

that's great, I've read through the article and rearranged my database so that it should now be normalised, it's looking much better now.

I only have one problem now. For example, the user that is logged in has said that they like aerobics and yoga, and I want to print out what they have chosen on a different page. So I've got the code:

   $result = mysql_query("SELECT sports.sport FROM sports, sports_relations 
   WHERE sports_relations.relatedID = ". $_SESSION['member_ID'] ."
   AND sports.sportID = sports_relations.relatedSportID");
   $array=mysql_fetch_assoc($result);
   foreach($array as $key => $value) {
   print "$value";
   }

When this prints the value only yoga is printed and not aerobics. I don't think I've got my loop right but I'm not sure how to rearrange....

Piranha

I would do it something like this:

$sql = "SELECT sports.sport FROM sports, sports_relations
   WHERE sports_relations.relatedID = ". $_SESSION['member_ID'] ."
   AND sports.sportID = sports_relations.relatedSportID";
$result = mysql_query($sql);
while ($row = mysql_fetch_array) {
    echo $row['sport'];
}

nc_brox

thanks piranha, now working perfectly!