Show DB Structure in a table

[Adamhumbug]

HI All,

I have noticed that when people show database structure on forums like this one, they have it as a table, like the following:

How do you create this?

[Barand]

Use MySQL from the command line client

[Adamhumbug]

Excellent - thanks so much.  Took me all day but i have learned how to get access to this and am now able to use it.  Thanks again

ssh username@host
mysql --host=dbhostname --user=dbusername --password=dbpassword dbname

 

[Barand]

Get a copy of MySQL Workbench. There's a right-click menu option to go straight to it.

[Adamhumbug]

9 minutes ago, Barand said:

Get a copy of MySQL Workbench. There's a right-click menu option to go straight to it.

I am using 1&1 as my host and keep getting:

  Lost connection to MySQL server at 'reading initial communication packet', system error: 0

[Barand]

Do I take it that this is from Workbench?

Have you checked host, user, password, port settings?

(You do your development on a remote host!?)

[Adamhumbug]

1 minute ago, Barand said:

Do I take it that this is from Workbench?

Have you checked host, user, password, port settings?

(You do your development on a remote host!?)

Hi, This is from workbench.  I get the same from sequel pro - both unable to connect yet i can with command line.

Yes the credentials are correct as i am using the same ones with command line.

I do indeed.  It was a whimsical purchase a while ago but yes it is still remote for the next 6 months.

[Barand]

I came across this when googling your error message

[Adamhumbug]

10 minutes ago, Barand said:

I came across this when googling your error message

Yes i saw that one, i have checked the file and that is already in there.

[ginerjm]

FWIW - how about this for a function that will give you an html table of the structure?

//****************************
$table_desc = Dump_Table_to_HTML('albany_data', '25_man_pool_players', $errmsg);
if ($table_desc === false)
{
	echo "Could not dump table - message is: $errmsg";
	exit();
}
echo "<div style='max-width:80%;margin-left:2%;'>";
echo "$table_desc<br>";
echo "</div>";
exit();
//****************************
function Dump_Table_to_HTML($db, $tblname, &$rtn_msg)
{
	//  check that the call is valid
	if ($db == '' || $tblname == '')
	{
		$rtn_msg = "Missing dbname or table name";
		return false;
	}
	//  make a db connection
	$pdo = PDOCOnnect($db);		// your function to make a db connection
	//  List the the field names and types for the specified table
	$q = "DESCRIBE $tblname";
	$tbl_desc = $pdo->query($q);
	//  Get all the rows from the query
	$cols = $tbl_desc->fetchall();
	//  initialize the var to hold the returned data
	$output = "Description of table: $tblname<br><br>";
	$output .= "<table border=1 style='border-collapse:collapse;'>";
	// add a bit of css to the html table cells
	$cell_style = "style = 'padding:2px 4px;'";
	//  get the first row of the query results
	$col =$cols[0];
	//  start the headings row of the html table output
	$output .= '<tr>';
	//  now loop thru this first query result row
	//  to get the table headings
	foreach($col as $heading=>$value)
		$output .= "<th $cell_style>$heading</th>";
	//  done with the headings
	$output .= '</tr>';
	//  Now loop thru all the query result rows just to get the values
	foreach ($cols as $col)
	{
		//  start a data row
		$output .= '<tr>';
		//  output all of the values for this row
		foreach($col as $heading=>$value)
			$output .= "<td $cell_style>$value</td>";
		//  end this row
		$output .= '</tr>';
	}
	//   end the html table
	$output .= '</table>';
	$output .= '<br>End of Structure';
	//  send the generated html table back to caller.
	return $output;
}

I tested it out with my own db and table so I know it works.  It may give you a tool for future use.

[ginerjm]

I"m sorry.  I wrote the code and tested it out and it worked great.  Only after I posted it did I realize that I could not "copy" it to anywhere where the visual structure remained the same.  So perhaps someone smarter than me knows how to take a web page image of this output and insert it somehow into a post (perhaps) without losing the proper formatting.

[Barand]

I have a similar one which I use to test queries when I can't be bothered to fire up Workbench.

It has some differences to yours

• I don't like functions that connect to the db, I prefer to pass the connection
• It works for any select query, not hardwired to DESCRIBE (although it will work with that)
• Uses prepared queries/parameters

function pdo2html(PDO $pdo, $sql, $params=[])
{
    $res = $pdo->prepare($sql);
    $res->execute($params);
    $data = $res->fetch();
    if ($data) {
        $out = "<table border='1'>\n";
        $heads = array_keys($data);
        $out .= "<tr><th>".join('</th><th>', $heads)."</th></tr>\n";
        do {
            $out .= "<tr><td>".join('</td><td>', $data)."</td></tr>\n";
        } while ($data = $res->fetch());
        $out .= "</table>\n";
    } else {
        $out = "NO RECORDS FOUND";
    }
    return $out;
}

Usage

<?php 
include 'db_inc.php';
$pdo = pdoConnect('humbug');
$role = 2;
?>
<html>
<head>
<style type='text/css'>
    table { border-collapse: collapse; font-family: sans-serif;}
    th    { background-color: #000; color: #FFF; padding: 8px; }
    td    { padding: 4px 8px; }
</style>
</head>
<body>
     <?= pdo2html($pdo, "DESCRIBE ssm_user")?>
     <br>
     <?= pdo2html($pdo, "SELECT * FROM ssm_user WHERE role_id = ?", [$role] )?>
</body>
</html>

Result

[ginerjm]

Continuing off-topic here:

I copied your code Barand and came up with a much better looking table BUT it still doesn't cut/paste into anything that maintains the format.  Is there a way to copy the output into a post on the forum?

[gizmola]

If  you mean HTML I am doubtful anything near pure html with css and js will work.  You could play with the Source view, but any forum is going to be extremely limited in what it allows you to put in.

I don't know how much trouble you want to go to, but sites like Codepen, jsfiddle, jsbin etc.  were built for that, so maybe that's a solution?

[gizmola]

4 hours ago, Adamhumbug said:

Hi, This is from workbench.  I get the same from sequel pro - both unable to connect yet i can with command line.

Yes the credentials are correct as i am using the same ones with command line.

I do indeed.  It was a whimsical purchase a while ago but yes it is still remote for the next 6 months.

This is apples to oranges.

1. 1.  SSH to a shell on your host
2. 2. Connect to localhost mysql server on port 3306 from shell on host

Trying with Workbench:

      1. Try to connect to localhost mysql on 3306 from your workstation

See the problem?

You could set up a port forward on your workstation, but Workbench and SQL pro both have some built in support for configuring forwarding in the client.

See this article among many others out there if you google:  https://www.digitalocean.com/community/tutorials/how-to-connect-to-a-mysql-server-remotely-with-mysql-workbench

 

 

[Barand]

53 minutes ago, ginerjm said:

Is there a way to copy the output into a post on the forum?

Did you mean something like this?

/**
* write query results to text table
* 
* @param PDO $pdo
* @param mixed $sql
* @param mixed $params
* @return query results
*/
function pdo2text(PDO $pdo, $sql, $params=[])
{
    $res = $pdo->prepare($sql);
    $res->execute($params);
    $data = $res->fetchAll(PDO::FETCH_ASSOC);
    if (count($data) == 0) {
        return "NO RECORDS FOUND";
    }
    $out = "<pre>\n";
    $heads = array_keys($data[0]);
    $widths = [];
    foreach ($heads as $h) {
        $widths[] = strlen($h);
    }
    foreach ($data as $row) {
        foreach (array_values($row) as $c => $v) {
            $widths[$c] = max($widths[$c], strlen($v));
        }
    }
    $horiz = '+';
    $format = '|';
    foreach ($widths as $w) {
        $horiz .= str_repeat('-', $w+2) . '+';
        $format .= " %-{$w}s |";
    }
    $format .= "\n";   
    $out .= "$horiz\n";
    $out .= vsprintf($format, $heads);
    $out .= "$horiz\n";
    foreach ($data as $row) {
        $out .= vsprintf($format, $row);
    }
    $out .= $horiz;
    
    return $out . '</pre>';
}

echo pdo2text($pdo, "SELECT * FROM ssm.user");