inspireddesign Posted June 4, 2009 Share Posted June 4, 2009 Hello all. Is there someone that can help with searching a database that has Multiple Tables and fields. I would like to search for the word %inspired% (say) in several tables and fields in the same database. I know how to connect and how to create the method of passing the value to the query but what I don't know is how to assemble the SQL for the purpose of searching multiple tables and fields (redundant right). Thanks for any help on the one. Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/ Share on other sites More sharing options...
Maq Posted June 4, 2009 Share Posted June 4, 2009 You need a JOIN and a WHERE clause. That's about the extent of the help I can give without more information, such as table and field names, what you want to extract, etc... Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/#findComment-849548 Share on other sites More sharing options...
inspireddesign Posted June 4, 2009 Author Share Posted June 4, 2009 That's about the extent of the help I can give without more information, such as table and field names The trouble is that there are about 50 tables and over 1000 fields that need searched. I was hoping I would be able to use wild cards or something to search all tables and fields within the database. Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/#findComment-849568 Share on other sites More sharing options...
Ken2k7 Posted June 4, 2009 Share Posted June 4, 2009 Then you need to structure the DB better? Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/#findComment-849579 Share on other sites More sharing options...
inspireddesign Posted June 4, 2009 Author Share Posted June 4, 2009 Is there not a way to handle this operation? I believe the DB is structured just fine. The application that we've developed is extremely LARGE. This is the my second time asking for help under this section and both times I've gotten "smart Alec" remarks. Is there a solution? This time can it be constructive please. Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/#findComment-849585 Share on other sites More sharing options...
kickstart Posted June 4, 2009 Share Posted June 4, 2009 Hi Struggle to think of a reason to search that many fields for a particular value, and suspect that you might start hitting limits if you tried in one statement. If you want to do it as one statement, then the crude way would be:- "SELECT "TableName1", Id FROM TableName1 WHERE Field1 = '$Search' OR Field2 = '$Search' UNION SELECT "TableName2", Id FROM TableName2 WHERE Field1 = '$Search' OR Field2 = '$Search' OR Field3 = '$Search' UNION SELECT "TableName3", Id FROM TableName3 WHERE Field1 = '$Search' UNION SELECT "TableName4", Id FROM TableName4 WHERE Field1 = '$Search' OR Field2 = '$Search' OR Field3 = '$Search' OR Field4 = '$Search'" But it would probably be better to loop through the tables, and in any case will be virtually unmaintainable. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/160975-search-query-multiple-tables/#findComment-849602 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.