dongwuli5105
2014-07-25 17:54
浏览 51

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?

图片转代码服务由CSDN问答提供 功能建议

我有2个表需要搜索,我在这里列出了重要的表和字段:

 表:常见问题解答
列:id,title 
 
表:FIELD_VALUES 
Columns:id,value 
   
 
 

所以基本上 表格常见问题解答包含一个问题“标题”,表格FIELD_VALUES包含可以与表格常见问题解答中的问题相关的信息。 因此,在这种情况下,“价值”是HTML格式的答案。 我需要做的是在表格FAQ中搜索“title”,并在表FIELD_VALUES中搜索“value”。 然后它应该只从FAQ常见问题解答中返回唯一的问题'title'。

我已经使用以下方法从常见问题解答返回'标题': < pre> SELECT title FROM FAQ WHERE title LIKE'%“。$ _ REQUEST ['term']。”%'“

这没关系,但是 我猜我需要做某种形式的UNION然后JOIN才能返回标题?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doushajian2018 2014-07-25 18:12
    已采纳

    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']."%'
    
    点赞 打赏 评论
  • duanpenpan5796 2014-07-25 18:05

    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.

    点赞 打赏 评论

相关推荐 更多相似问题