Hi,
I want to be able to do a full-text search on 2 tables in a database, but I'd like to combine the results in a specific way. Searching the first table isn't to difficult, and I have implemented this without too much trouble. This table(1) contains an ID, a referenceID, email address, description & keywords. I'd currently search this table using full-text search against the details & keywords fields, pulling out the referenceID & email address.
Now the next table(2) holds a history for each record in table1, so table2 could have multiple records relating to a single record in table1. Table2 has less fields, ID, referenceID & history, with referenceID being the field that links it to table1.
Example below
Table1
| ID |referenceID| email | description | keywords |
|001 | XYZ123 |
john@doe.com |this is a veg buyer| veg buyer|
|002 | ABC456 |joe@bloggs.com|this is a veg seller|veg seller|
Table2
| ID |referenceID| history |
|001 | XYZ123 |purchased 1 cabbage|
|002 | XYZ123 |purchased 10 carrots|
|003 | XYZ123 |purchased 20 potatoes|
|004 | ABC456 |sold 20 parsnips|
|005 | ABC456 |sold 10 carrots|
What I'd like to do, is be able to search table1.details, table1.keywords & table2.history, and display the results. But as I need to pull out an email address with each result, I need to cross reference the results from table2 with table1. I.e. If I search for 'parsnips' I need to get the referenceID ABC456 returned, from table2, along with
joe@bloggs.com from table1.
What is the best way to go about this? Performing separate queries, or using a JOIN/UNION?
I'm using MySQL 5.05, with full-text indexing enabled on the relivant fields, the search page is a php form. The code I'm using the search table1 is:
"SELECT referenceID,email, MATCH(description,keywords) AGAINST('$search') AS score FROM table1 WHERE MATCH(description,keywords) AGAINST('$search' IN BOOLEAN MODE) ORDER BY score DESC";
Many thanks
Ben