skaterdav85 Posted May 20, 2010 Share Posted May 20, 2010 I am using MySQL and I need to search through all fields of a DB. Is there a way to do this without having to write separate select statements for each table? Link to comment Share on other sites More sharing options...
chibineku Posted May 20, 2010 Share Posted May 20, 2010 There are probably better ways to do it on PHPMyAdmin, but if you are using a server side language, say PHP (because it's the one I have any knowledge of) you could do it like this: $tables_sql = "SHOW TABLES FROM database";$tables_res = mysqli_query($conn, $tables_sql) or die("Error: ".mysqli_error($conn));$result = '';while($table = $tables_res) {$search_sql = "SELECT * FROM $table WHERE something LIKE '%something_else%'";$search_res = mysqli_query($conn, $search_sql) or die("Error: ".mysqli_error($conn));if(mysqli_num_rows($search_res) > 0) {foreach($search_res as $k -> $v) {$result .= "$k: $v";}}mysqli_free_result($search_res);mysqli_free_result($search_sql);} That's untested, and I'm sure full of errors, but it's the basic idea. Someone will tell me if it's BS. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 20, 2010 Share Posted May 20, 2010 No, it's not possible to define an index or use MATCH with more than one table. chibineku's code is a way to query the tables automatically without needing to type each one yourself, but it still searches each table individually. Link to comment Share on other sites More sharing options...
skaterdav85 Posted May 20, 2010 Author Share Posted May 20, 2010 ya i thought about that, except the part where you have 'something' will need to be dynamic, and the field name could vary from table to table, thus making it hard to use a script like this. I dont know advanced SQL but maybe MySQL's version of T-SQL could do something like this? justsomeguy, would you have any knowledge of advanced sql in mysql? Link to comment Share on other sites More sharing options...
justsomeguy Posted May 20, 2010 Share Posted May 20, 2010 There's not really "advanced" SQL, but one thing you might want to look into is using a stored procedure to do all of the work in MySQL instead of through PHP. I guess that could be considered advanced. You could send the SP the term you're searching for, and it would do the searching and return the results.The problem with this is that you can only return one set of results, so you need to decide the format of that result set. The result might have 3 fields, one for the table name it came from, one for the field name, and one for the value that matched. If you wanted to return all of the fields from the records that matched in any table then your result set would have to include the fields for all tables, so it would be a pretty big result set. Link to comment Share on other sites More sharing options...
skaterdav85 Posted May 20, 2010 Author Share Posted May 20, 2010 ok i can try that. do other db systems like sql server provide easier ways of doing such? Link to comment Share on other sites More sharing options...
justsomeguy Posted May 20, 2010 Share Posted May 20, 2010 Not that I know of, I don't know of any way to search through multiple tables without explicitly specifying them. Link to comment Share on other sites More sharing options...
Synook Posted May 21, 2010 Share Posted May 21, 2010 Err, note that if you have a good database design you generally shouldn't need to search through multiple tables - all the information needed for a certain purpose should be contained within a single table or expressible using relations. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.