select * question

[aniket_dj]

Hi all,

 

I have a db called "core_set" which has a bunch of tables in it.

 

Basically I want to search for a table named "x" or "xx" or "xxx" inside db "core_set". I am scripting using PHP, but I guess this is purely a (My)SQL question.

 

I was hoping for something like

 

select *from db(core_set) where tables_in_core_set like '%x%';

 

right now 'show tables' returns me the names of tables, but I cant have select *from show tables  as such.

 

Any ideas of how I can get around this?

 

Thanks!

[craygo]

first you should select a table and store it as a variable. say you have a form, you submit the form, you retrieve the table you selected in the form.

 

$table = $_GET['tablename'];

 

now you can get the results from the table

 

$sql = "SELECT * FROM `$table`";

 

you would use show tables first to show a list of the tables then you would select the table to get the results.

 

lets say you want to list the tables and when you click on a table name it shows the data

<?php
$sql = "SHOW TABLES FROM $dbname";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_row($res)){
echo "<a href=\"".$_SERVER['PHP_SELF']."?tablename=".$r[0]."\">".$r[0]."</a><br />\n";
}
if(isset($_GET['tablename'])){
$table = $_GET['tablename'];
$sql = "SELECT * FROM `$table`";
$res = mysql_query($sql) or die(mysql_error());
$fields = mysql_num_fields($res);
  while($r = mysql_fetch_array($res)){
    for($i=0; $i<$fields; $i++){
    echo $r[$i]." ";
    }
  }
}
?>

 

Ray

[Barand]

try

<?php
$sql = "SELECT C.TABLE_NAME 
        FROM information_schema.`COLUMNS` C
        WHERE C.TABLE_SCHEMA=DATABASE() 
        AND C.TABLE_NAME LIKE '%x%' ";
?>