Jump to content

Show DB Structure in a table


Adamhumbug

Recommended Posts

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.

Link to comment
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.

Link to comment
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.

Link to comment
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

Link to comment
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?

Link to comment
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

 

 

Link to comment
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");

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.