jQuery UI autocomplete, search 2 tables with PHP/MySQL

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?

I'm not entirely sure what you want to achieve... what is the relation between these two tables? is it one to many (one faq with many values)? if so, then what is the name of the foreign key in FIELD_VALUES? — Kamil Janowski
– Kamil Janowski, Commented Jul 25, 2014 at 18:00
1 'title' will only ever have 1 'value' related to it by the 'id'. In an ideal world I would change the db structure but I cant. — generalcb
– generalcb, Commented Jul 25, 2014 at 18:04
ok. So if I understood correctly both FAQ and Value always exist and have the same ids right? so what you need is not union, but join try this: select f.title, fv.value from FAQs f inner join FIELD_VALUES fv on f.id=fv.id where title LIKE '%".$_REQUEST['term']."%'" — Kamil Janowski
– Kamil Janowski, Commented Jul 25, 2014 at 18:09
you might also want to read about SQL injections, because what you're doing here is pretty dangerous — Kamil Janowski
– Kamil Janowski, Commented Jul 25, 2014 at 18:10
Yes I will look at doing it all safely. I just wanted to get the correct way of doing it. I will give your suggestion a whirl as soon as I get a spare moment. — generalcb
– generalcb, Commented Jul 25, 2014 at 18:21

Niklas · Accepted Answer · 2014-07-25 18:12:25Z

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

answered Jul 25, 2014 at 18:12

Niklas

1,7851 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

generalcb Over a year ago

You understand correctly, I am looking for the title to be returned in all cases. Let me give it a try.

G Duggan · Accepted Answer · 2014-07-25 18:21:27Z

0

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.

edited Jul 25, 2014 at 18:21

answered Jul 25, 2014 at 18:05

G Duggan

287 bronze badges

4 Comments

generalcb Over a year ago

I only want to return the 'title' in the dropdown. So if the search matches something in the 'title' that 'title' displays. If the search matches something in the 'value', the 'title' it is related to by 'id' is displayed. If the search matches both the 'title' and 'value', just the 'title' is displayed.

G Duggan Over a year ago

Got it, so a foreign key linking the value back to the title is still required. Currently it looks like you have primary key id's on both tables but nothing linking them together. You could use 2 queries with logic to achieve this.

generalcb Over a year ago

Changing the DB structure is not an option unfortunately.

G Duggan Over a year ago

Are they already linked? Is the FIELD_VALUES id = FAQs id? If so I will change 'faqId_FK' to simply 'id' in the example query above. Also Niklas' answer should work as well.

Collectives™ on Stack Overflow

jQuery UI autocomplete, search 2 tables with PHP/MySQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related