Why doesnt this complex MySQL query work?

Question

I have 2 tables; members and teams

members table memberID, firstName, lastName

(firstName and lastName are fulltext indexes)

teams table team_id, member1ID, member2ID

Here's my query

$sql = "SELECT a.* ";       
$sql .= "FROM teams a WHERE ";
$sql .= "a.member1ID IN (SELECT b.memberID FROM members b ";
$sql .= "WHERE MATCH(b.firstName, b.lastName) AGAINST('$q' IN BOOLEAN MODE)) ";
$sql .= "OR a.member2ID IN (SELECT b.memberID FROM members b ";
$sql .= "WHERE MATCH(b.firstName, b.lastName) AGAINST('$q' IN BOOLEAN MODE)) ";

if($year)
    $sql .= "AND a.team_y = $year ";

$sql .= "ORDER BY a.team_num ASC "; 

if($limit)      
    $sql .= "$limit";

This query has to be close, but its not working yet.

Im trying to build a query that will let me show me all of the teams "$q" is on.

Ex. $q=doe , Show me all teams that doe is on.

This query has to output the teams.

Answer 1

One possible reason your query doesn't work is there is a minimum length on full-text searching, which defaults to 4 characters. "doe" would fail this match. You can increase this via variable "ft_min_word_len"
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_ft_min_word_len

By the way, if you want to avoid Normalizing (which isn't always the "best" way to go), you could at least use JOIN's instead of sub-selects.. e.g. (field names renamed to save on typing)

select t.* from teams t
inner join members me1 on t.m1 = me1.id
inner join members me2 on t.m2 = me2.id
where MATCH(me1.fname, me1.lname, me2.fname, me2.lname) 
    AGAINST('smith' IN BOOLEAN MODE);