Jump to content

searching through an entire db


skaterdav85
 Share

Recommended Posts

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

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...