How do I get the MySQL table structure in PHP? Plus a list of all tables?

Question

What query do I need to run in PHP to get the structure of a given table in the database? And what query do I need to run to get a list of all the tables?

Answer 1

To get a list of columns for a table, use the DESCRIBE SQL statement. The syntax is as follows:

DESCRIBE TableName

To get a list of tables on the database, use this SQL statement:

SHOW TABLES

Answer 2

$q = mysql_query('DESCRIBE tablename');
while($row = mysql_fetch_array($q)) {
    echo "{$row['Field']} - {$row['Type']}\n";
}

found it at http://www.electrictoolbox.com/mysql-table-structure-describe/

Answer 3

To get the CREATE syntax use

SHOW CREATE TABLE table_name;

Also take a look in the information_schema database. Lots of very useful information about your databases, tables, indexes, etc.

See: How to find all the tables in MySQL with specific column names in them?

Answer 4

For get comments of the fields you can use:

SHOW FULL COLUMNS FROM table_name;

Notice keyword FULL, this is what makes MySQL to include privileges and comments info into the response.

Answer 5

Updating with MYSQLI:

Connect with database

function conectadb($banco) 
{
$endereco = "localhost";
$usuario  = "root";
$senha    = "";
try
{
    $con = new mysqli($endereco, $usuario, $senha, $banco);
    $con->set_charset("utf8"); // acentuação
    return $con;
}
catch (Exception $e)
{
    echo "<h1>Falha</h1><br/>";
    echo $e->getMessage();
    die();
}
}

Show column table:

function show_table($tabela)
{
$conexao=conectadb('venda');
$sql = "DESCRIBE $tabela";
$result = $conexao->query($sql);
while ($coluna = $result->fetch_assoc()) 
{
    echo "<p>".$coluna['Field']." - ";
    echo $coluna['Type']."</p>";
}

}

Answer 6

$result= mysqli_query($conn, "DESCRIBE your_table");
 while($table = mysqli_fetch_array($result)) 
{ 
  echo($table[0]." ".$table[1]." ". $table[2]." ". $table[3]." ". $table[4]);  
 }