Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
02-19-2009, 02:30 PM #1
- Join Date
- Dec 2008
- Thanked 0 Times in 0 Posts
Searching and Cross Referencing Multiple Tables
Following on from my other post; I have the full-text search working, but now need to do some advanced searches, but I'm not sure how to go about putting the query together.
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 (one > many relationship). Table2 has less fields, ID, referenceID & history, with referenceID being the field that links it to table1.
| ID |referenceID| email | description | keywords |
|001 | XYZ123 | email@example.com |this is a veg buyer| veg buyer|
|002 | ABC456 |firstname.lastname@example.org|this is a veg seller|veg seller|
| 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 email@example.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";