php導出字典

public  function promotion_list()
    {
        $sql =     "CREATE TABLE `ka_sys` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sys_name` varchar(45) CHARACTER SET utf8 NOT NULL COMMENT '系統中文名稱',
  `eng_name` varchar(65) DEFAULT NULL,
  `sys_logo1` varchar(65) CHARACTER SET utf8 DEFAULT NULL COMMENT '大圖',
  `sys_logo2` varchar(65) CHARACTER SET utf8 DEFAULT NULL COMMENT '小圖',
  `sys_num` varchar(60) DEFAULT NULL,
  `created` int(11) NOT NULL COMMENT '創建時間',
  `updated` int(11) NOT NULL COMMENT '更新時間',
  `deleted` enum('1','0') NOT NULL DEFAULT '0' COMMENT '凍結',
  `parent_id` int(11) DEFAULT '0' COMMENT '系統父ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1";
        preg_match_all("/(`.+`)(.+)('.+'),/i",$sql,$out);
        echo '<pre>';
        print_r($out);
    }//
    
    public function create_database_doc()
    {
       //  /*
          header("Content-Type:   application/msword;charset=gbk");       
          header("Content-Disposition:   attachment;   filename=doc.doc");       
          header("Pragma:   no-cache");       
          header("Expires:   0");       
       
        
        // */
          
          $sql_query = "show tables;";
         
          $query     = $this->db->query($sql_query);
          $rowset    = $query->result_array();
          $tablearrs    = array();
          foreach($rowset as $key => $value)
          {
              $sql_query = "desc ". $value['Tables_in_ka'];
           //   echo $sql_query;
              $query     = $this->db->query($sql_query);
              $reset     = $query->result_array();
              $this->get_notes($value['Tables_in_ka'], $reset);
              $tablearrs[$key]['tab_name'] = $value['Tables_in_ka'];
              $tablearrs[$key]['feild_info'] = $reset;
          }
          $output ='';
          foreach ($tablearrs as $key => $value)
          {
                 $output   .=   '表'.$tablearrs[$key]['tab_name'].PHP_EOL;
                $output    .=   '<table border="1" cellspacing="0" cellpadding="0"  width="90%" align="center">';    
                $output   .=   '<tr><td><strong>Field</strong></td>'
                            . '<td><strong>Type</strong></td>'
                            . '<td><strong>Null</strong></td>'
                            . '<td><strong>Key</strong></td>'
                            . '<td><strong>Default</strong></td>'
                            . '<td><strong>Extra</strong></td>'
                            . '<td><strong>notes</strong></td></tr>';   
                foreach ($tablearrs[$key]['feild_info'] as $ky => $val)
                {
                      $output   .=   '<tr><td>'.$val['Field'].'</td>'
                            . '<td>'.$val['Type'].'</td>'
                            . '<td>'.$val['Null'].'</td>'
                            . '<td>'.$val['Key'].'</td>'
                            . '<td>'.$val['Default'].'</td>'
                            . '<td>'.$val['Extra'].'</td>'
                            . '<td>'.$val['notes'].'</td></tr>';
                }
                
               
                $output   .=   '</table>'.PHP_EOL;
          }
         //   $output    =   mb_convert_encoding($output, "GBK","UTF-8");
          echo   $output;       
 
    }//end func create_database_doc
    
    public function get_notes($table_name,&$reset)
    {
         $sql_query = "show create table ".$table_name;
         //echo $sql_query;
         $query     = $this->db->query($sql_query);
         $rowset    = $query->result_array();
         $sql       = $rowset[0]['Create Table'];
         preg_match_all("/`(.+)`(.+)['(.*)'],/i",$sql,$out);
     
         
         foreach ($reset as $key => $val)
         {                   
              @preg_match_all("/^(.*)\'([^\']*)/i",$out[2][$key],$t_out);
              if( count($reset)-count($out[2])<=2)
              {
                  if(strpos($reset[$key]['Field'], 'id')!==FALSE  && count($reset)-count($out[2])==1)
                       @preg_match_all("/^(.*)\'([^\']*)/i",$out[2][$key-1],$t_out);
                      @$reset[$key]['notes']  =$t_out[2][0];
              }
              else
              {
                    @$reset[$key]['notes']  ='';
              }
              
              
         }
      
         
         
    }//end func get_notes

//----------------------------------------------------------------------------------------

<?php
/**
 * 生成mysql數據字典
 */
header ( "Content-type: text/html; charset=utf-8" );
 
// 配置數據庫
$dbserver = "localhost";
$dbusername = "root";
$dbpassword = "123";
$database = "db153";
 
// 其他配置
$title = '數據字典';
 
$mysql_conn = @mysql_connect ( "$dbserver", "$dbusername", "$dbpassword" ) or die ( "Mysql connect is error." );
mysql_select_db ( $database, $mysql_conn );
mysql_query ( 'SET NAMES utf8', $mysql_conn );
$table_result = mysql_query ( 'show tables', $mysql_conn );
// 取得所有的表名
while ( $row = mysql_fetch_array ( $table_result ) ) {
    $tables [] ['TABLE_NAME'] = $row [0];
}
 
// 循環取得所有表的備注及表中列消息
foreach ( $tables as $k => $v ) {
    $sql = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.TABLES ';
    $sql .= 'WHERE ';
    $sql .= "table_name = '{$v['TABLE_NAME']}'  AND table_schema = '{$database}'";
    //echo $sql.'<br/>';
    $table_result = mysql_query ( $sql, $mysql_conn );
    while ( $t = mysql_fetch_array ( $table_result ) ) {
        $tables [$k] ['TABLE_COMMENT'] = $t ['TABLE_COMMENT'];
    }
     
    $sql = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.COLUMNS ';
    $sql .= 'WHERE ';
    $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
     
    $fields = array ();
    //echo $sql.'<br/>';
    $field_result = mysql_query ( $sql, $mysql_conn );
    while ( $t = mysql_fetch_array ( $field_result ) ) {
        $fields [] = $t;
    }
    $tables [$k] ['COLUMN'] = $fields;
}
mysql_close ( $mysql_conn );
 
$html = '';
// 循環所有表
foreach ( $tables as $k => $v ) {
    // $html .= '<p><h3>'. $v['TABLE_COMMENT'] . '&nbsp;</h3>';
    $html .= '<table  border="1" cellspacing="0" cellpadding="0" align="center">';
    $html .= '<caption>' . $v ['TABLE_NAME'] . '  ' . $v ['TABLE_COMMENT'] . '</caption>';
    $html .= '<tbody><tr><th>字段名</th><th>數據類型</th><th>默認值</th>
    <th>允許非空</th>
    <th>自動遞增</th><th>備注</th></tr>';
    $html .= '';
     
    foreach ( $v ['COLUMN'] as $f ) {
        $html .= '<tr><td class="c1">' . $f ['COLUMN_NAME'] . '</td>';
        $html .= '<td class="c2">' . $f ['COLUMN_TYPE'] . '</td>';
        $html .= '<td class="c3">&nbsp;' . $f ['COLUMN_DEFAULT'] . '</td>';
        $html .= '<td class="c4">&nbsp;' . $f ['IS_NULLABLE'] . '</td>';
        $html .= '<td class="c5">' . ($f ['EXTRA'] == 'auto_increment' ? '是' : '&nbsp;') . '</td>';
        $html .= '<td class="c6">&nbsp;' . $f ['COLUMN_COMMENT'] . '</td>';
        $html .= '</tr>';
    }
    $html .= '</tbody></table></p>';
}
 
// 輸出
echo '<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>' . $title . '</title>
<style>
body,td,th {font-family:"宋體"; font-size:12px;}
table{border-collapse:collapse;border:1px solid #CCC;background:#6089D4;}
table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }
table th{text-align:left; font-weight:bold;height:26px; line-height:25px; font-size:16px; border:3px solid #fff; color:#ffffff; padding:5px;}
table td{height:25px; font-size:12px; border:3px solid #fff; background-color:#f0f0f0; padding:5px;}
.c1{ width: 150px;}
.c2{ width: 130px;}
.c3{ width: 70px;}
.c4{ width: 80px;}
.c5{ width: 80px;}
.c6{ width: 300px;}
</style>
</head>
<body>';
echo '<h2 style="text-align:center;">' . $title . '</h2>';
echo $html;
echo '</body></html>';
 
?>

mysqli

<?php

/**

 * 生成mysql數據字典

 */

header ( "Content-type: text/html; charset=utf-8" );

// 配置數據庫

$dbserver = "192.168.5.220";

$dbusername = "root";

$dbpassword = "root";

$database = "card";

// 其他配置

$title = '數據字典';

$mysql_conn = new mysqli( "$dbserver", "$dbusername", "$dbpassword","$database" ) ; 

$mysql_conn->query ( 'SET NAMES utf8'  );

$table_result = $mysql_conn->query  ( 'show tables' );

// 取得所有的表名

while ( $row = $table_result->fetch_array (  ) ) {

    $tables [] ['TABLE_NAME'] = $row [0];

}

// 循環取得所有表的備注及表中列消息

foreach ( $tables as $k => $v ) {

    $sql = 'SELECT * FROM ';

    $sql .= 'INFORMATION_SCHEMA.TABLES ';

    $sql .= 'WHERE ';

    $sql .= "table_name = '{$v['TABLE_NAME']}'  AND table_schema = '{$database}'";

    //echo $sql.'<br/>';

    $table_result = $mysql_conn->query ( $sql  );

    while ( $t = $table_result->fetch_array (  ) ) {

        $tables [$k] ['TABLE_COMMENT'] = $t ['TABLE_COMMENT'];

    }

    $sql = 'SELECT * FROM ';

    $sql .= 'INFORMATION_SCHEMA.COLUMNS ';

    $sql .= 'WHERE ';

    $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";

    $fields = array ();

    //echo $sql.'<br/>';

    $field_result = $mysql_conn->query ( $sql );

    while ( $t = $field_result->fetch_array (  ) ) {

        $fields [] = $t;

    }

    $tables [$k] ['COLUMN'] = $fields;

}

  $mysql_conn->close();;

$html = '';

// 循環所有表

foreach ( $tables as $k => $v ) {

    // $html .= '<p><h3>'. $v['TABLE_COMMENT'] . '&nbsp;</h3>';

    $html .= '<table  border="1" cellspacing="0" cellpadding="0" align="center">';

    $html .= '<caption>' . $v ['TABLE_NAME'] . '  ' . $v ['TABLE_COMMENT'] . '</caption>';

    $html .= '<tbody><tr><th>字段名</th><th>數據類型</th><th>默認值</th>

    <th>允許非空</th>

    <th>自動遞增</th><th>備注</th></tr>';

    $html .= '';

    foreach ( $v ['COLUMN'] as $f ) {

        $html .= '<tr><td class="c1">' . $f ['COLUMN_NAME'] . '</td>';

        $html .= '<td class="c2">' . $f ['COLUMN_TYPE'] . '</td>';

        $html .= '<td class="c3">&nbsp;' . $f ['COLUMN_DEFAULT'] . '</td>';

        $html .= '<td class="c4">&nbsp;' . $f ['IS_NULLABLE'] . '</td>';

        $html .= '<td class="c5">' . ($f ['EXTRA'] == 'auto_increment' ? '是' : '&nbsp;') . '</td>';

        $html .= '<td class="c6">&nbsp;' . $f ['COLUMN_COMMENT'] . '</td>';

        $html .= '</tr>';

    }

    $html .= '</tbody></table></p>';

}

// 輸出

echo '<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>' . $title . '</title>

<style>

body,td,th {font-family:"宋體"; font-size:12px;}

table{border-collapse:collapse;border:1px solid #CCC;background:#6089D4;}

table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }

table th{text-align:left; font-weight:bold;height:26px; line-height:25px; font-size:16px; border:3px solid #fff; color:#ffffff; padding:5px;}

table td{height:25px; font-size:12px; border:3px solid #fff; background-color:#f0f0f0; padding:5px;}

.c1{ width: 150px;}

.c2{ width: 130px;}

.c3{ width: 70px;}

.c4{ width: 80px;}

.c5{ width: 80px;}

.c6{ width: 300px;}

</style>

</head>

<body>';

echo '<h2 style="text-align:center;">' . $title . '</h2>';

echo $html;

echo '</body></html>';

?>