Searching Multiple Tables

[lpollard]

I've tried many different queries and now I'm really stuck. I have 3 tables and will be adding more tables to search later on but I want to find out what the mysql query is, or what i have to do to search different columns from each table. Here are the three tables that I currently have:

 

Clients

| cl_id | tableid | cl_name | other fields...

 

Events

| ev_id | tableid | ev_name | other fields...

 

Userss

| usr_id | tableid | usr_first | usr_last | other fields...

 

Now when i want to display my search results I need to get

'usr_id' 'ev_id' 'cl_id' as one column;

'tableid' as one column;

'cl_name', ev_name', 'user_first & user_last' as one column.

 

If that doesn't make sense, or you need more info please let me know, thanks.

[AyKay47]

what does your query look like now?

you will want to use a JOIN here..

[lpollard]

I tried looking up joins and trying a couple and couldn't work out which one had to be used. I've either got the syntax wrong or just got the whole statement/join wrong but I haven't currently got a query as i deleted it  :-\

[lpollard]

I've just looked over trash and found one where I tried a union but failed at that too 

 

SELECT * FROM events UNION SELECT tableid, cl_name, cl_id FROM clients UNION SELECT tableid, usr_name, usr_id FROM users

WHERE events.ev_name like '%$trimmed%' OR clients.cl_name like '%$trimmed%' OR users.user_first like '%$trimmed%'

 

[fenway]

Write the query for each table separately, then UNION.

[lpollard]

Just tried and it searches the fields across the three tables, but is only returning the table id. And also it doesn't return the 3 tables together, it'll return a search result for one table only.

 

SELECT tableid, ev_name, ev_id FROM events WHERE ev_name LIKE '%$trimmed%'  UNION
SELECT tableid, usr_first, usr_id FROM users WHERE usr_first LIKE '%$trimmed%' UNION
SELECT tableid, cl_name, cl_id FROM clients WHERE cl_name LIKE '%$trimmed%'
LIMIT $start, $limit

[fenway]

That LIMIT won't apply to anything but the last time the way it's written.

[lpollard]

Sorry i'm so confused on what i should do here, I don't know UNION or JOIN functions all that well. What do you think it should be?

[fenway]

I don't know -- do you want to LIMIT each to 3, or all to 3?

 

Which table should "win"?

[lpollard]

I think 'all'. But i'm not sure what you mean by which table to win. 

I appologise for beeing a noob on this matter, but i'll try and explain what i'm trying to do a bit better.

---

I have 3 mysql tables with different data that needs to be searched into 1 results table.

 

events.ev_id          users.usr_id      clients.cl_id          Need to be in one column of the results table

events.ev_name    users.usr_first    clients.cl_name      Needs to be displayed in the 2nd column of the results table

events.tableid        users.tableid        clients.tableid        Needs to be displayed in the 3rd column.

---

I need to search each table with a where statement like:

WHERE cl_name LIKE '%$trimmed%'
WHERE ev_name LIKE '%$trimmed%'
WHERE usr_first LIKE '%$trimmed%'

---

All rows from each table need to be displayed in the results table, the rows displayed in the results table needs to be limited.

and my php version is 5.1.56

 

I hope that makes it a bit clear, and my sincerest appologies again.

[fenway]

I don't care about the output.

 

I need to know how many results you want back from EACH table.

 

And how many results you want in TOTAL.

[lpollard]

Need to get back all of them that fit the criteria, and display 10.

[fenway]

Which 10 -- sorted by what?