Problem with MYSQL searching functions.

[lefteyecc]

OK hey guys. It's me again. I have a problem with my mysql search queries. Now I am doing this on my own with no outside helo other than a crappy book I'm reading. Now I know that I can do fulltext searching but I was wiondering if there was another way to get results from my tables. Also is there a limit on how long your query should be. I'll post the queries below for reference.

here is the main query that get's generated by my script depending on form input.

$query = "SELECT * FROM $table WHERE MATCH($extra) AGAINST('*$str_what*')";

Now it doesn't look complicated nor does it lok liek it gets much done but it works for some words that I search for. BTW I have 3 tables with fulltext indexes on all of them so then next query uses those indexes.

$query = "(SELECT DISTINCTROW * FROM $table[0],$table[1],$table[2] WHERE
                   MATCH($extra0) AGAINST('+(*(($str_what)(*))))' IN BOOLEAN MODE))
            UNION (SELECT DISTINCTROW * FROM $table[0],$table[1],$table[2] WHERE
                   MATCH($extra1) AGAINST('+(*(($str_what)(*))))' IN BOOLEAN MODE))
            UNION (SELECT DISTINCTROW * FROM $table[0],$table[1],$table[2] WHERE
                   MATCH($extra2) AGAINST('+(*(($str_what)(*))))' IN BOOLEAN MODE))";

and here is the wrray that I use to create the search locations.

$table = array($files_table, $main_table, $links_table);
        $extra0 =    $table[0].'.'.path.','.$table[0].'.'.added.','.$table[0].'.'.edited.
                 ','.$table[0].'.'.type.','.$table[0].'.'.description;
        $extra1=$table[1].'.'.message.','.$table[1].'.'.created.','.$table[1].'.'.edited.
                 ','.$table[1].'.'.author;
        $extra2=$table[2].'.'.name.','.$table[2].'.'.category.
                 ','.$table[2].'.'.created.','.$table[2].'.'.url.
                 ','.$table[2].'.'.description;

 

[TonyGroves]

What is the problem here?

If it's giving inaccurate search results, maybe it could be due to your use of leading asterisks in the search strings. According to the manual, only trailing asterisks are supported. Though I don't know if that would have any effect.

Regarding the length of queries, there is no limit. However, your query could be shortened (and possibly speeded up) by using[tt] WHERE MATCH ... OR MATCH ... OR MATCH ... [/tt]instead of all those UNIONs.

 

[lefteyecc]

Oh that I understand...but I wanna learn about unions in mysql so I used that. Hmm I am still ont he fence as to the best way to search for results. Currently I get about 1000 or more results when using the array query.

 

[TonyGroves]

You could order your results according to relevance (the MATCH function returns a relevance rating from 0 upwards), and use LIMIT to get the most relevant records.

 

[lefteyecc]

Ok I found a resolution to my problem. I simply user an array of queries and then extracted the results from each array. I works suprisignly well now. Thanks for the help guys.

P.S If anyone wants to see the solution just ask and I'll post it. Just in case anyone else is having the same or similiar issue.