php/mysql get all tables and columns in a database

Question

i want to check a database and get all table names first and then show some kind of report from data inside each table

        $query = $db3->query("SELECT `table_name` from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db3' ") or die("$db3->error");
        while ( $table = $query->fetch_assoc() )
        {
            //echo $table['table_name'].'<br />';
             $t = trim($table['table_name']);
            //i get all table names now i want to select all columns from that table name :
            $select = $db3->query("SELECT * FROM ".$t." ") or die($db3->error);
            $row = $select->fetch_assoc();
            // checking to see if query worked fine
            echo gettype($row);
            //it returns NULL
             }

am i doing it the right way ?

what should i do ?

You can check http://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables It might help you! — hriziya, Feb 01 '14 at 07:52
thanks ... but i dont need only the column name i want to extraxt the data from the table — k961, Feb 01 '14 at 07:55

score 1 · Accepted Answer · answered Feb 01 '14 at 07:48

1

For security reasons you should have a whitelist of databases/tables you want to generate reports from. Querying for all tables assumes that all future tables will need to be part of this system.

You can query for the columns in each table using show columns from tableName and iterate the results.

answered Feb 01 '14 at 07:48

helion3

34,737
15
57
100

yeah im only generating the results from this database (db3) witch is only for reports and no GET OR POST is in the query so im safe from sql injections ... – k961 Feb 01 '14 at 07:51

score 0 · Answer 2 · answered Feb 01 '14 at 07:52

0

You can use this query in mysql

mysql> SELECT table_name, table_type, engine

-> FROM information_schema.tables

-> WHERE table_schema = 'db5'

-> ORDER BY table_name DESC;

answered Feb 01 '14 at 07:52

Ashouri

906
4
19

i already have the table name i want to extract data of the table that im getting ...$t = trim($table['table_name']); //i get all table names now i want to select all columns from that table name : $select = $db3->query("SELECT * FROM ".$t." ") or die($db3->error); $row = $select->fetch_assoc(); // checking to see if query worked fine echo gettype($row); – k961 Feb 01 '14 at 07:58

score 0 · Answer 3 · answered Nov 22 '15 at 03:37

SELECT table_name, column_name FROM Information_schema.columns WHERE table_name like '%example%' ORDER BY table_name DESC

Or you can try this

SELECT table_name, column_name FROM Information_schema.columns WHERE column_name like '%example%' ORDER BY table_name DESC

php/mysql get all tables and columns in a database

3 Answers3