0

I am building a Mysql Full Text Search, I have 4 tables to search. when I use only single table in search query it works, but when i use multiple tables in query it doesn't work. also I dont want to use UNION ALL in query cause i am using check box select option to perform search in individual or multiple tables.

MySQL Query on multiple table not working.

$sqlquery = mysql_query("(SELECT * FROM table1, table2, table3, table4 WHERE MATCH (pflink, title) AGAINST ('%$keyword*%' IN BOOLEAN MODE) )ORDER by pflink desc, $orderby $sortby LIMIT $rowsperpage OFFSET $offset ")or die (mysql_error());

It gives me error Column 'pflink' in where clause is ambiguous

Using single table Query works.

$sqlquery = mysql_query("(SELECT * FROM table1 WHERE MATCH (pflink, title) AGAINST ('%$keyword*%' IN BOOLEAN MODE) )ORDER by pflink desc, $orderby $sortby LIMIT $rowsperpage OFFSET $offset ")or die (mysql_error());

Html Checkbox codes

  <input name="all-tables" type="checkbox" value="true" checked="checked" id="check-all" >
  <input name="table1" type="checkbox" value="true" disabled="disabled" >
  <input name="table2" type="checkbox" value="true" disabled="disabled" >
  <input name="table3" type="checkbox" value="true" disabled="disabled" >
  <input name="table4" type="checkbox" value="true" disabled="disabled" >

Please suggest any possible way to modify this query to make it work for multiple tables.

Thanks.

  • because your mysql engine is confused that to which table pflink belongs :) – WatsMyName Aug 10 '12 at 08:35
  • @Sabin pflink and title are in all tables, in fact all tables have same structure but different values. –  Aug 10 '12 at 08:36
  • i hope you've defined index on each 4 tables??. Again if you have all tables with same structure, pflink is still ambiguous, because you've not defined which table pflink refers to. Moreover, so far I know, full text search won't work in multiple tables. For similar discussion http://stackoverflow.com/questions/1241602/mysql-match-across-multiple-tables – WatsMyName Aug 10 '12 at 08:48

1 Answers1

0

Long time back i came to similar conditions. I had multiple tables to use full text, but I couldnt figure out the way to do what i want. I searched but couldnt find the way. So finally I came up with following idea

1. Make another table say "keywords", with fields title, description , item_id, and the table data belongs to.
2. make a code to fetch all title and description from table1,table2 and so on and insert it to keywords.
3. Make keywords table suitable for full text search.
4. when user searches, search keyword table. Retrieve relevant value from table1, table 2 etc from the recordset (as you get item id and table from the full text search query).

The advantage of doing this is query will be faster and easier.

Hope this helps

WatsMyName
  • 4,240
  • 5
  • 42
  • 73
  • Your solution is like making a single table which have all tables data, and then make full text search. –  Aug 10 '12 at 09:35
  • Yes i had different tables with similary structure but different value. First table contains value related to clothes, second related to shoes, third related to cosmetics, fourth related to books. And i have a one text box like google does, upon searching i have to display result from all tables. I found this way effecting and easier.. infact this is only the solution i came up with. I wrote a cron job to truncate keywords table and selecting all values from all tables and insert it again to keywords table. You can manually run this code as well once in a while. – WatsMyName Aug 10 '12 at 09:39
  • do you know a way around for single search query that can check for category like `WHERE MATCH(pflink= somecategory, title)` –  Aug 10 '12 at 10:05
  • Do you mean this? `SELECT * FROM table WHERE MATCH(pflink, title) AGAINST("categoryname");` – WatsMyName Aug 10 '12 at 10:16
  • `SELECT * FROM all_tables WHERE MATCH (pflink, title) AGAINST ('%$keyword*%' IN BOOLEAN MODE) HAVING pflink LIKE '%somecategory%'` this is working for query not for `SELECT COUNT(*) FROM all_tables WHERE MATCH (pflink, title) AGAINST ('%$keyword*%' IN BOOLEAN MODE) HAVING pflink LIKE '%somecategory%' ` –  Aug 10 '12 at 10:19