MySQL/PHP Refining join query for searching multiple tables

Question

I need to perform a search across 18 separate SQL tables for a string. The search will be looking in differently named fields and multiple fields on some tables.

While I can achieve this with something like the below (the first 2 tables), I feel the code is going to get very unwieldy once all 18 tables are joined.

Is there any way I can refine this code?

Also when the result is given can I find out with PHP which table the answer came from?

mysql_query("SELECT a.about,b.title,b.article,b.description

FROM about a 
JOIN articles b ON b.user=a.user

WHERE (upper(a.about)  LIKE'%".$val."%')  OR  (upper(b.title)  LIKE'%".$val."%') OR  (upper(b.article)  LIKE'%".$val."%') OR  (upper(b.descirption)  LIKE'%".$val."%')  ORDER BY $order $max");

Which kind of content is this? Why is it in 18 different tables? Feels like it's a database design problem, first of all. — ItalyPaleAle
– ItalyPaleAle, Commented Mar 9, 2014 at 16:10
I agree with @Qualcuno, sounds like a database design flaw. As to, "find[ing] out with PHP which table the answer came from", you could always label your selected columns with a prefix: "a.about as a_about". — Will
– Will, Commented Mar 9, 2014 at 16:12
@user1209203 Google "mysql full text". A starter could be: blog.marceloaltmann.com/… If you use "LIKE" MySQL can't use indexes and so the search can be PAINFULLY slow (as the db needs to go through all rows every time) — ItalyPaleAle
– ItalyPaleAle, Commented Mar 9, 2014 at 21:38

Hardy · Accepted Answer · 2014-03-09 16:13:24Z

1

Use UNION

SELECT about from some WHERE about like '%some%'
UNION 
SELECT about from some_other WHERE about like '%some%'

and if you want to allow duplicate results use UNION ALL

answered Mar 9, 2014 at 16:13

Hardy

5,6412 gold badges20 silver badges28 bronze badges

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

Comments

Tom Fenech · Accepted Answer · 2014-03-09 16:41:45Z

0

take a look at the UNION operation.

select x from table_1 where blabla
union
select y from table_2 where blabla

and so on.

x and y must be the same format.

edited Mar 9, 2014 at 16:41

Tom Fenech

75.1k13 gold badges119 silver badges154 bronze badges

answered Mar 9, 2014 at 16:18

Iovor

1

1 Comment

Guesser Over a year ago

@lovor I am selecting different fields from each table though

Collectives™ on Stack Overflow

MySQL/PHP Refining join query for searching multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related