1

is it possible for a mysql statement to select any field from any table ? for example : SELECT * FROM * WHERE ? IN (SELECT * FROM *) to implement a generic search method which doesn't depends on a specific table or field. i've just read that we can list all tables of a database with a, SQL command : SHOW TABLES Do you have any idea about how can we do this ? Thank you :)

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • Does it have to be only in sql or you can use something like php? – Marc Oct 23 '12 at 17:36
  • This is covered in this post, there are multiple solutions. http://stackoverflow.com/questions/562457/search-for-all-occurrences-of-a-string-in-a-mysql-database – mikeswright49 Oct 23 '12 at 17:38
  • Thank you @Marc :) No, it can be in PHP (but only in PHP because i'm working with it) –  Oct 23 '12 at 17:39
  • @mikeswright49 : thank you ! i'm reading that post now :) –  Oct 23 '12 at 17:47

2 Answers2

0

Create a procedure and use dynamic query mechanism e.g. below:

 CREATE PROCEDURE dynamicQuery()
  BEGIN
  SET @tableName := 'TABLENAME1';
  SET @SqlStr = 'SELECT  * FROM @tableName';
  PREPARE n_StrSQL FROM @SqlStr;
  EXECUTE n_StrSQL;
  DEALLOCATE PREPARE n_StrSQL;
 END
Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
  • Thank you :) but it's like if i do : `SELECT * FROM table1, table2, ...., table200 WHERE ? IN (SELECT * FROM table1, table2, ...., table200);` isn't it ? –  Oct 23 '12 at 17:47
  • @Mehdi: No, its like running `select * from Table1` or `select * from Table2`. All I meant to say is that you can construct a dynamic query sing variables and execute them. Its up to you, what query you want to construct. – Yogendra Singh Oct 23 '12 at 17:49
0

You could try something like this in php

$getTables = mysql_query("show tables");
$tmpString = '';
while ($table_data = mysql_fetch_row($getTables))
{
    $tmpString.=$table_data[0].',';
}   
$ALL_DATABASE_TABLES = substr($string,0,strlen($tmpString)-1); //Remove the last ,

Then you got all your tables and you can build your query

like this

$qry = "SELECT * FROM $ALL_DATABASE_TABLES"
Marc
  • 16,170
  • 20
  • 76
  • 119