2

I just need to know how to print out the table names and columns, everything I try doesn't work even though this is apparently very simple. I've looked everywhere and I can't find anything that will print out the columns. Can anyone help?

bard_the_dragon
  • 33
  • 1
  • 5
  • Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38679580). Its a stored proc call. – Drew Jul 31 '16 at 01:19

2 Answers2

4

The following code should pull out the information you're after.

<?php
$mysqli = new mysqli("hostname", "username", "password", "database");

if($mysqli->connect_errno)
{
    echo "Error connecting to database.";
}

// Gather all table names into an array.

$query = "SHOW TABLES";

$result = $mysqli->query($query);
$tables = $result->fetch_all();

// Step through the array, only accessing the first element (the table name)
// and gather the column names in each table.
foreach($tables as $table)
{
    echo "<h2>" . $table[0] . "</h2>";

    $query = "DESCRIBE " . $table[0];
    $result = $mysqli->query($query);

    $columns = $result->fetch_all();

    foreach($columns as $column)
    {
        echo $column[0] . "<br />";
    }
}

$mysqli->close();
?>
aylnon
  • 371
  • 2
  • 8
1

The MySQL syntax for those uses the command SHOW which can be used to show the tables in the selected database (SHOW tables;) or output table structure (SHOW tableName;).

Here is functional code to see the tables in the current database so long as you update the connection details (first line of code): 

$mysqli = new mysqli("server.com", "userName", "password", "dbName");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$query = "SHOW tables;";
$result = $mysqli->query($query);
/* associative array */
$rows = $result->fetch_all(MYSQLI_ASSOC);
var_export($rows);

$result->free();

/* close connection */
$mysqli->close();
Julie Pelletier
  • 1,740
  • 1
  • 10
  • 18
  • I know how to use mysql to display it, it was a question of how to output it in a php script. – bard_the_dragon May 14 '16 at 02:23
  • Sorry, your question certainly didn't show what you knew or had tried. Look at http://php.net/manual/en/mysqli.quickstart.prepared-statements.php for good examples of how to run SQL queries from PHP (5th is probably the simplest for your case). – Julie Pelletier May 14 '16 at 02:25
  • Have already looked at that, didn't help. I just need to know how to print out the columns and table names, I've tried using fetch_assoc, I've tried mysqli_fetch_row, I've tried a bunch of stuff. Sorry if this isn't very coherent. (or if I sound kind of arrogant) I tried the example to print out query results and it didn't work for some reason. – bard_the_dragon May 14 '16 at 02:32
  • For further assistance, please provide the code you tried that failed. – Julie Pelletier May 14 '16 at 02:34
  • I've tried so much, really I just need to know how to use mysqli:result to print out the columns and table names. – bard_the_dragon May 14 '16 at 02:36
  • You should try the code I just made for you. I updated my answer. – Julie Pelletier May 14 '16 at 02:58
  • Thanks, will try it now. – bard_the_dragon May 14 '16 at 03:02
  • How do I not make it print the array code around the table names? – bard_the_dragon May 14 '16 at 03:22
  • I did a `var_export()` so that you could look at the output. Now that you can see what it looks like, you should be able to do your outputs like in every single PHP script you've done before (mainly with `echo` inside a `foreach`). If you're a bit unfamiliar with those, I'd recommend following a PHP MySQL tutorial that uses Mysqli. – Julie Pelletier May 14 '16 at 03:47