lpollard Posted October 26, 2011 Share Posted October 26, 2011 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. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/ Share on other sites More sharing options...
AyKay47 Posted October 26, 2011 Share Posted October 26, 2011 what does your query look like now? you will want to use a JOIN here.. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282456 Share on other sites More sharing options...
lpollard Posted October 26, 2011 Author Share Posted October 26, 2011 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 :-\ Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282461 Share on other sites More sharing options...
lpollard Posted October 26, 2011 Author Share Posted October 26, 2011 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%' Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282489 Share on other sites More sharing options...
fenway Posted October 27, 2011 Share Posted October 27, 2011 Write the query for each table separately, then UNION. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282555 Share on other sites More sharing options...
lpollard Posted October 27, 2011 Author Share Posted October 27, 2011 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 Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282598 Share on other sites More sharing options...
fenway Posted October 27, 2011 Share Posted October 27, 2011 That LIMIT won't apply to anything but the last time the way it's written. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282641 Share on other sites More sharing options...
lpollard Posted October 27, 2011 Author Share Posted October 27, 2011 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? Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282750 Share on other sites More sharing options...
fenway Posted October 27, 2011 Share Posted October 27, 2011 I don't know -- do you want to LIMIT each to 3, or all to 3? Which table should "win"? Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282807 Share on other sites More sharing options...
lpollard Posted October 27, 2011 Author Share Posted October 27, 2011 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. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1282817 Share on other sites More sharing options...
fenway Posted October 28, 2011 Share Posted October 28, 2011 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. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1283000 Share on other sites More sharing options...
lpollard Posted October 31, 2011 Author Share Posted October 31, 2011 Need to get back all of them that fit the criteria, and display 10. Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1283767 Share on other sites More sharing options...
fenway Posted November 1, 2011 Share Posted November 1, 2011 Which 10 -- sorted by what? Link to comment https://forums.phpfreaks.com/topic/249857-searching-multiple-tables/#findComment-1283890 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.