Easiest way to search in all columns of multiple tables?

Question

I have many tables:

d_customers
d_customers
d_news
d_pages
d_products
d_projects
d_sms

and I want to create a search form to search for any word typed in it in all columns of all tables... But when write SQL code I see that it's long and confusing... Can any one tell me the right way to do this?

'SELECT * FROM d_customers,d_customers,d_news,d_pages,d_products,d_projects,d_sms
WHERE ' . $nc_make . 'LIKE .....
AND LIKE.... AND LIKE.....  AND LIKE.....  AND LIKE.....  '

i want to search into all coulmns in all tables by LIKE word...if i search for google word i want to select all coumns in all tables where all columns like google

nooo..i want to search with like to find my words in all coulmns...this code dont search with like...SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'YOUR_DATABASE' — user1080247, Jun 01 '13 at 12:47
if i have one word like google and want to search all tables where any word like google how to do this ? — user1080247, Jun 01 '13 at 12:48

score 0 · Answer 1 · answered Jun 01 '13 at 13:04

0

create table t1(a int);
create table t2(a int, b int);
insert into t1 values (1);
insert into t2 values (1,3);

SELECT *
  FROM (
         (select 't1' as tbl, a as Col1, null as Col2 from t1) 
         union
         (select 't2' as tbl, a as Col1,    b as Col2 from t2)
       ) as U
where U.Col1 = 1 or U.Col2 = 1

Result:

TBL COL1 COL2
t1  1    (null)
t2  1    3

answered Jun 01 '13 at 13:04

Andrey Volk

3,513
2
17
29

i get error... #1222 - The used SELECT statements have a different number of columns ..... i think its about columns types because union used with Similar columns types ..have u any another solution because i cant change columns types – user1080247 Jun 01 '13 at 14:47
As you can see, the t1 table has only one column, but the t2 table has two. When you build your query, you have to have same columns count for each table, so `null as ColX` is used. Build a query depend on maximum count of columns. – Andrey Volk Jun 01 '13 at 14:53

score -1 · Answer 2 · answered Jun 01 '13 at 12:27

If the tables are related to each other then join them and them apply your condition.

select *
from customers c
inner join pages p
on p.customer_id=c.customer_id
where customer_name like 'xyz'

You cannot avoid joins and conditions in the sql if they are necessary but you can optimize them.

If you want to generate query dynamically using programming and then want to execute then in mysql information_schema stores all the information related to the tables and the fields contained in that table. You can use it to generate your dynamic sql.

Hope this helps.

i want to search into all coulmns in all tables by LIKE word...if i search for google word i want to select all coumns in all tables where all columns like google — user1080247, Jun 01 '13 at 12:55

Easiest way to search in all columns of multiple tables?

2 Answers2