Jump to content
#StayAtHome ×
Adamhumbug

Show DB Structure in a table

Recommended Posts

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:

1222820005_Screenshot2020-01-10at11_57_53.thumb.png.d293761bfa9d540d900a5be65b9e21f8.png

How do you create this?

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

  • Great Answer 1

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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!?)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
10 minutes ago, Barand said:

I came across this when googling your error message

image.png.b718c2541d0357f14bae8b11cfecc2e7.png

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

image.png.12d01f2b08aea79097cccaab5b4ed4f1.png

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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

 

 

Share this post


Link to post
Share on other sites
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");

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.