Php mysql does not match results

Question

Ok, so I'm trying to do a full-text search in my mysql table. Here is the request

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 of lines that contain either a greeting in the header or a joke, but I don't seem to get any results. It doesn't matter what word I'm looking for.

I also tried removing AND flags < 5 , as well as ups DESC, downs ASC LIMIT 0,30

Both do not work.

This is what I do with 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 worked for every other request I threw 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

Since 'welcome' is STOPWORD , so MySQL skips searching for this word.

See the list of MySQL notes. 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, if its length is less than 4, by default MySQL will also skip for search.

For example, "like me", "usa", "php", "job", etc. (more about ft_min_word_len )

Answer 2

I'm not too sure that this will make a big difference, but have you tried using Boolean Mode? Like this:

 AGAINST ('welcome' IN BOOLEAN MODE)