searching user database in 3rd normal form

[wil831]

i have created a database with the following tables:

table_user:

uid | uname

1 | william
2 | john

table_music:

mid | genre

1 | rock
2 | jazz
3 | alternative
4 | pop
5 | techno

table_music_relation:

rid | mid | uid

1 | 2 | 1
2 | 4 | 1
3 | 5 | 1
4 | 1 | 2
5 | 5 | 2

if i have a search form with checkboxes of all the music genres,
what's the best way to return uname when a user searches by music genre?

[ab4net]

i think there is easier way:
make a table like:

table_user:

id | name |music |

1 | william |rock |
2 | william |jazz |
3 | john |rock |

so u can enter many times the name of the singer regarding the music ..

when the user checkes in the CHECK BOX : JAZZ as example:

if ($jazz=="ON"){
$statment="SELECT *from table_user where music='jazz'";
}

and u can repeat it regarding all the music types u have:

if ($jazz=="ON" and $classic="ON"...ect

[wil831]

ab4net, thanks for the info, but I think that way would create too much data redundancy because I do have a lot of other fields in my table_user like email, url, etc. My search form uses an array for the music genre search selection, like this...

echo "<td><input type=\"checkbox\" name=\"mid[]\" value=\"$mid\">$genre</td>";

so this is what i came up with so far:

if (isset($mid[0])) {
	$result = mysql_query("SELECT uid FROM table_music_relations WHERE mid IN ('" . implode("','", $mid) . "')");
	while (list($uid) = mysql_fetch_row($result)) {
		$result2 = mysql_query("SELECT uname FROM table_user WHERE uid='$uid'");
		list($uname) = sql_fetch_row($result2);
		if (mysql_num_rows($result2) > 0) {
			echo "$uname<br>";
		} else { echo "No users found"; }
	}
}

the problem with the code i have right now is that it echo's "No users found" for every user that doesn't match the search...

[ahundiak]

Maybe something like this:

  /* The query */
  $genre = "'rock','alternative','techno'";

  $set = mysql_query(
    "SELECT table_music.m_id,table_user.u_id,table_user.uname " .
    "FROM table_music,table_user,table_music_relation " .
    "WHERE genre IN ($genre) " .
    "AND table_music_relation.m_id = table_music.m_id " .
    "AND table_music_relation.u_id = table_user.u_id "
  );

  if (mysql_errno()) die (mysql_error());

  while($row = mysql_fetch_assoc($set)) {
    echo 'ROW ',$row['m_id'],' ',$row['u_id'],' ',$row['uname'],'<br />',"\n";
  }

[mattr]

Just as an aside, drop the rid column in table_music_relation:
( mid | uid )