jQuery UI自动完成，用PHP / MySQL搜索2个表

Question

I have 2 tables that need to be searched, I have listed the important tables and fields here:

Table: FAQs
Columns: id, title

Table: FIELD_VALUES
Columns: id, value

So basically the table FAQs holds a question 'title' and the table FIELD_VALUES holds information that can be related to the question in the table FAQs. So 'value' in this case is an answer in HTML format. What I need to do is search the table FAQs for the 'title', and search the table FIELD_VALUES for the 'value'. It should then only return unique question 'title' from the table FAQs.

I have gotten as far as returning the 'title' from FAQs using:

SELECT title FROM FAQs WHERE title LIKE '%".$_REQUEST['term']."%'"

That works ok, but I am guessing I need to do some form of UNION and then a JOIN to return the title?

Answer 1

I understand it as you want the title returned in all cases. Either if the search matches the title in FAQs, the value in the FIELD_VALUES or if both matches. Then you should do a join:

SELECT FAQs.title FROM FAQs
JOIN FIELD_VALUES ON FIELD_VALUES.id = FAQs.id
WHERE FAQs.title LIKE '%".$_REQUEST['term']."%' OR
FIELD_VALUES.value LIKE '%".$_REQUEST['term']."%'