FULLTEXT search probs

Cornelia · #1 (**permalink**) 03-23-07, 05:53

Hi,
I have a problem with a fulltext search. When trying to search for strings like 2007-03-23, all the rows in the database are shown (even if they don't include the date or numbers). If I search for names or words, the search works just the way I wants it.

$sql = "SELECT * FROM `private` WHERE MATCH (date, names, words) AGAINST ('$search' IN BOOLEAN MODE) order by id DESC";

Do anybody know how I can solve this problem? Have I done something wrong?

Cornelia · #2 (**permalink**) 03-26-07, 06:31

*bump*

please?

aschk · #3 (**permalink**) 03-26-07, 06:55

Firstly : surely your date column is of type date or datetime ???
Secondly : if it's not a date and is instead a string then have you enabled fulltext searching on it?

If it is a date then you can't do a fulltext search on it.

Also bear in mind that MySQL might see that as a calculation
i.e. 2007 - 3 - 23 = 1981

How it treats that i'm not sure. I'm guessing you are using inverted commas to keep it a string test.

Cornelia · #4 (**permalink**) 03-26-07, 07:42

Firstly : surely your date column is of type date or datetime ???
Secondly : if it's not a date and is instead a string then have you enabled fulltext searching on it?

The column is of the type date and I have made a fulltext index of the column together with some other columns.

Hmm.. if I can't do a fulltext search, can I do like this? (for example)

Code:

if (preg_match("/^\"?(\d{2})-(\d{2})-(\d{4})\"?$/",$fritext)) {
$search = explode("-", $fritext);
$search = "`".$search[2]."-".$search[1]."-".$search[0]."`";

$sql  = "SELECT * FROM `private` WHERE date = $search order by id DESC";
$result = mysql_query($sql) or die("SQL: $sql <br>".mysql_error());
}
 
else {
$search = $fritext;

$sql  = "SELECT * FROM `private` WHERE MATCH (name, date, words) AGAINST ('$search' IN BOOLEAN MODE) order by id DESC";
$result = mysql_query($sql) or die("SQL: $sql <br>".mysql_error());
}

Thanks in advance!

aschk · #5 (**permalink**) 03-26-07, 07:57

I'm guessing going by the preg_match statement that users a typing in the date i.e. 26-3-2007 or 26/3/2007 or 26/03/07 .

My recommendation is that if they need to put a date in constrict their options with dropdown boxes (thus giving you the date format you require). The reason being as I demonstrated just above is that you never know how your date format is going to come in.

Thus your SQL statement should become
SELECT * FROM `private` WHERE date='{$date}' AND MATCH(name,words) AGAINST ('{$search}' IN BOOLEAN MODE) ORDER BY id DESC

n.b. i'm assuming dd-mm-yyyy . Which might not be the case with your preg statement. It could be mm-dd-yyyy . Beware of this as it will perform a bad query.

aschk · #6 (**permalink**) 03-26-07, 08:03

Ooh I could be wrong here. Never tried two WHERE clauses with FULLTEXT searching.

aschk · #7 (**permalink**) 03-26-07, 08:05

Either way you should NOT be fulltext searching on a date column. Fulltext is only really applicable for TEXT types where you're trying to find an instance of a phrase inside it. For short phrases use LIKE('%variable%')

Cornelia · #8 (**permalink**) 03-26-07, 09:48

Hi again,
thanks for all the help!

I dropped the fulltext thing for the date column and made two different actions depending on the input. If it's a date I only do a search in the date column, and if it's a word or name, I do a fulltext search instead.

Btw, I have a calendar (Tigra calendar) that writes out dates in the wrong order, thats why I have to change the order in the search

Thanks again!

Internet Services

Web Development

Digital Marketing

Consumer Tech