利用PHP生成MySQL数据库表结构信息
<?php
$db = '';
$base = '';
$data = show_db($db, $base);
echo($data);
function show_db($db, $base)
{
$data = '<h1>数据库设计说明书(' . $db . ')</h1>';
$tables = show_tables($base);
foreach ($tables as $key => $val) {
$table = current($val);
$table_annotation =current(table_annotation($table,$base))['table_comment'];
$data .= '<table border="1" cellspacing="0">';
$data .= '<caption><h2>' . $key . $table .$table_annotation. '</h2></caption>';
$data .= '<tr bgcolor="#cccccc"><th width="130">字段名称</th><th width="200">类型(长度)</th><th width="180">主键</th><th width="80">不是空</th><th width="80">默认</th><th width="80">注释</th></tr>';
$fields = describe($table, $base);
foreach ($fields as $item) {
$data .= '<tr><td>' . $item['column_name'] . '</td><td>' . $item['column_type'] . '</td><td>' . $item['column_key'] . '</td><td>' . $item['is_nullable'] . '</td><td>' . ($item['column_default'] ? $item['column_default'] : ' ') . '</td><td>' . ($item['column_comment'] ? $item['column_comment'] : ' ') . '</td></tr>';
}
$data .= '</table>';
$data .= '<br/>';
}
$data .= '';
return $data;
}
function show_tables($base)
{
$sql = "SHOW TABLES";
return query($sql, $base);
}
function describe($table, $base)
{
$sql = "SELECT column_name,column_type, column_key, is_nullable, column_default, column_comment, character_set_name FROM information_schema.COLUMNS WHERE table_schema = '$base' AND table_name = '$table'";
return query($sql, $base);
}
function table_annotation($table, $base)
{
$sql = "SELECT table_comment FROM information_schema.tables WHERE table_schema = '$base' AND table_name = '$table'";
return query($sql, $base);
}
function query($sql, $base)
{
$mysqli = mysqli_connect("127.0.0.1", "root", "123456", $base);
$res = mysqli_query($mysqli, $sql);
if (!$res) {
die('Invalid query: ' . mysqli_error());
}
$list = array();
while ($row = mysqli_fetch_assoc($res)) {
$list[] = $row;
}
return $list;
}
?>