php mysql match against no results

Question

Alright, so I'm trying to do a Full Text search on my mysql table. Here is the query

SELECT *,
       MATCH (title, joke) AGAINST ('welcome') AS relevance,
       MATCH (title) AGAINST ('welcome') AS title_relevance
FROM jokes
WHERE MATCH (title, joke) AGAINST ('welcome') 
AND flags < 5
ORDER BY title_relevance + relevance + ups DESC, downs ASC LIMIT 0, 30

and here is my table

CREATE TABLE IF NOT EXISTS `jokes` (
  `jid` varchar(24) NOT NULL,
  `uid` int(11) NOT NULL,
  `title` mediumtext NOT NULL,
  `joke` longtext NOT NULL,
  `ups` int(11) NOT NULL DEFAULT '0',
  `downs` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `createddate` int(11) NOT NULL,
  `editdate` int(11) NOT NULL,
  PRIMARY KEY (`jid`),
  FULLTEXT KEY `searcher` (`title`,`joke`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have a couple rows that contain either welcome in the title, or in the joke, but I don't seem to get any results. It doesn't matter what word I search for.

I've also tried removing AND flags < 5 and also ups DESC, downs ASC LIMIT 0,30

Both don't seem to work.

This is what I do with the php code

if($st->prepare($SearchQuery))
{
    if(!$st->execute())
        ChromePhp::log("Execute Error: " . $st->error);
    else
    {
        $results = fetchAll($st);
        $ret = new stdClass();
        $ret->TotalCount = 0;
        $ret->Results = $results;
        return $ret;
    }
}

and this is fetchAll(which has worked for every other query I've tossed at it).

function fetchAll($result)
    {    
        $array = array();

        if($result instanceof mysqli_stmt)
        {
            $result->store_result();

            $variables = array();
            $data = array();
            $meta = $result->result_metadata();

            while($field = $meta->fetch_field())
                $variables[] = &$data[$field->name]; // pass by reference

            call_user_func_array(array($result, 'bind_result'), $variables);

            $i=0;
            while($result->fetch())
            {
                $array[$i] = array();
                foreach($data as $k=>$v)
                    $array[$i][$k] = $v;
                $i++;

                // don't know why, but when I tried $array[] = $data, I got the same one result in all rows
            }
        }
        elseif($result instanceof mysqli_result)
        {
            while($row = $result->fetch_assoc())
                $array[] = $row;
        }

        return $array;
    }

Anyone have any ideas what I'm doing wrong here?

Thanks.

Answer 1

I'm not too sure if it will make much of a difference, but have you tried using Boolean Mode? Like this:

AGAINST ('welcome' IN BOOLEAN MODE)

Answer 2

Because 'welcome' is a STOPWORD, so that MySQL skips to search for this word.

See the stopword list of MySQL Full text search here

http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html

And please remind the length of words in the search phrase also, if its length less than 4, by default MySQL will skips for search it too.

For example "the way I am", "usa", "php", "job" etc (read more about ft_min_word_len)