[jonnekke]

Hi there,

I'm making a full text search for my website. Now I get a problem if the
table are not build with the same fields.

F.E.

table_1 -> title, story, comments
table_2 -> title, story, comments, links
table_3 -> title, comments, tips
table_4 -> comments, links, transportation

PHP Code:

$search_result_algemeen = mysql_query(

"SELECT * FROM table_1 WHERE MATCH(title, story, comments) AGAINST($word IN BOOLEAN MODE) 

 UNION SELECT * FROM table_2 WHERE MATCH(title, story, comments) AGAINST($word IN BOOLEAN MODE)

 UNION SELECT * FROM table_3 WHERE MATCH(title, comments, tips) AGAINST($word IN BOOLEAN MODE)

 UNION SELECT * FROM table_4 WHERE MATCH(comments, links, transportation) AGAINST($word IN BOOLEAN MODE)

 ") or die('Error, query failed'); 

It should be something like above..
but there are some errors... where did I go wrong and is there a way to do somthing similar?

_j

[job0107]

Try it like this:

PHP Code:

$search_result_algemeen = mysql_query( 
"SELECT * FROM table_1 WHERE MATCH(title, story, comments) AGAINST('$word' IN BOOLEAN MODE)  
 UNION SELECT * FROM table_2 WHERE MATCH(title, story, comments) AGAINST('$word' IN BOOLEAN MODE) 
 UNION SELECT * FROM table_3 WHERE MATCH(title, comments, tips) AGAINST('$word' IN BOOLEAN MODE) 
 UNION SELECT * FROM table_4 WHERE MATCH(comments, links, transportation) AGAINST('$word' IN BOOLEAN MODE) 
 ") or die('Error, query failed'); 

[wirehopper]

You could use SHOW COLUMNS and build a query with all character fields.

I can't test this because my server is running MySQL 4 - but if you have 5.0+, this might be worth a try.

SHOW COLUMNS FROM `evo_items__item` WHERE `Type` REGEXP "^varchar";

MySQL :: MySQL 5.0 Reference Manual :: 12.5.5.5 SHOW COLUMNS Syntax

The approach would be to run the SHOW COLUMNS query to get the character and varchar columns (as well as any others you want), then construct your text search query to search those columns. That way, as you add more tables, and more columns, the code doesn't have to change. You can also use the SHOW TABLES command to find new tables.

Naming the tables and columns can help here. For example, you wouldn't want to allow most users to search the user table, so you could prefix private table names with an underscore.