searching through an entire db

[skaterdav85]

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?

[chibineku]

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.

[justsomeguy]

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.

[skaterdav85]

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?

[justsomeguy]

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.

[skaterdav85]

ok i can try that. do other db systems like sql server provide easier ways of doing such?

[justsomeguy]

Not that I know of, I don't know of any way to search through multiple tables without explicitly specifying them.

[Synook]

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.