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

问题描述：

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?

我有2个表需要搜索，我在这里列出了重要的表和字段： p>

 表：常见问题解答
列：id，title 
 
表：FIELD_VALUES 
Columns：id，value 
  code>  pre> 
 
 所以基本上 表格常见问题解答包含一个问题“标题”，表格FIELD_VALUES包含可以与表格常见问题解答中的问题相关的信息。 因此，在这种情况下，“价值”是HTML格式的答案。 我需要做的是在表格FAQ中搜索“title”，并在表FIELD_VALUES中搜索“value”。 然后它应该只从FAQ常见问题解答中返回唯一的问题'title'。 p> 
 
 
我已经使用以下方法从常见问题解答返回'标题'： p> 
 
   SELECT title FROM FAQ WHERE title LIKE'％“。$ _ REQUEST ['term']。”％'“
  code>  pre> 
 
 
这没关系，但是 我猜我需要做某种形式的UNION然后JOIN才能返回标题？ p> 
  div>

答

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']."%'

答

If you add a foreign key to your FIELD_VALUES table you should be able to achieve what you want:

Table: FAQs Columns: id, title

Table: FIELD_VALUES Columns: id, faqId_FK, value

Then your SQL would look like:

$title = SELECT * FROM FAQs WHERE title LIKE '%". $_REQUEST['term']."%';
if(empty($title)) {
  $valueId = SELECT faqId_FK FROM FIELD_VALUES WHERE value LIKE '%". $_REQUEST['term']."%';
  if(!empty($valueId)) {
    $title = SELECT title FROM FAQs WHERE id = $valueId;
  }
}  

echo $title

Without some kind of link between your tables it will be impossible to tell which values go with which titles.

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

相关推荐