PHP mysql查询工具

PHP基于PDO的 mysql 查询工具
单页面实现，将页面放在任意目录即可。
访问用户 admin 密码 password
代码很简单，主要为了在没有phpMyAdmin时方便执行SQL。
效果如下：
代码如下：
  1 <?php
  2 
  3 // 权限验证
  4 $user_list = [
  5     'admin' => 'password',
  6 ];
  7  
  8 $user = $_SERVER['PHP_AUTH_USER'];
  9 $pass = $_SERVER['PHP_AUTH_PW'];
 10 
 11 if (empty($user_list[$user]) || $user_list[$user] != $pass)
 12 {
 13     header('WWW-Authenticate: Basic realm="My Realm"');
 14     header('HTTP/1.0 401 Unauthorized');
 15     die ("Not authorized");
 16 }
 17 
 18 // 参数
 19 $row_limit = @$_GET['row_limit']  ?: 1000;    // 显示行数限制
 20 $str_limit = @$_GET['str_limit']  ?: 100;    // 显示值长度限制
 21 $db_config = [
 22     'connect'  => @$_POST['db_connect']  ?: '',
 23     'user'     => @$_POST['db_user']     ?: '',
 24     'password' => @$_POST['db_password'] ?: '',
 25 ];
 26 
 27 $sql = @$_POST['sql'] ?: '';
 28 
 29 // 查询SQL 
 30 function runSql($db, $sql)
 31 {
 32     $dt_begin = round(microtime(true) * 1000);
 33 
 34     $con = new PDO($db['connect'], $db['user'], $db['password'], [
 35         PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES `utf8`',
 36         PDO::ATTR_PERSISTENT => FALSE,
 37     ]);
 38 
 39     $con -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 40     $con -> setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 41     
 42     $sql = trim($sql);
 43     
 44     $sql_type = strtoupper(explode(' ', $sql)[0]);
 45     
 46     if (strpos($sql, 'INTO OUTFILE') !== FALSE)
 47     {
 48         $sql_type = 'OUTFILE';
 49     }
 50 
 51     $cmd = $con -> prepare($sql);
 52     
 53     $cmd -> execute();
 54 
 55     $return = NULL;
 56     if ($sql_type == 'SELECT' || $sql_type == 'SHOW' || $sql_type == 'DESC')
 57     {
 58         $return = $cmd -> fetchAll(PDO::FETCH_ASSOC);
 59     }
 60     else if ($sql_type == 'INSERT')
 61     {
 62         $return = $con -> lastInsertId();
 63     }
 64     else
 65     {
 66         $return = $cmd -> rowCount();
 67     }
 68     
 69     $dt_begin = round(microtime(true) * 1000) - $dt_begin;
 70     $res = [
 71         'use_time' => $dt_begin,
 72         'data' => $return,
 73     ];
 74     
 75     return $res;
 76 }
 77 
 78 // 执行SQL
 79 $res = null;
 80 $error = '';
 81 if (!empty($sql))
 82 {
 83     try
 84     {
 85         $res = runSql($db_config, $sql);
 86     }
 87     catch(Exception $ex)
 88     {
 89         $error = $ex;
 90     }
 91 }
 92 
 93 // 格式化值输出
 94 function f($v)
 95 {
 96     global $str_limit;
 97 
 98     $row_count = count(explode("
", $v));
 99 
100     $v = htmlspecialchars($v);
101     $v = nl2br($v);
102 
103     if (strlen($v) > $str_limit)
104     {
105         $v = substr($v, 0, $str_limit) . '...';
106     }
107 
108     if ($row_count > 1)
109     {
110         $v = '<div style="text-align:left;">' . $v . '</div>';
111     }
112 
113     return $v;
114 }
115 
116 ?>
117 <!DOCTYPE html>
118 <html lang="zh-CN">
119 <head>
120 <meta charset="utf-8">
121 <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
122 <title>DB 查询工具</title>
123 <meta name="renderer" content="webkit">
124 <meta name="viewport" content="width=device-width, initial-scale=1">
125 <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js"></script>
126 <style>
127 body{
128     line-height:1.5;
129     font-family:consolas, Courier New;
130 }
131 input[type=text], input[type=password], textarea{
132     font-family:consolas, Courier New;
133     padding:3px;
134 }
135 </style>
136 </head>
137 <body>
138     <h1 style="margin:5px 0; font-size:16px;">MySql 查询工具</h1>
139     <form id="frmQuery" method="POST">
140     <input type="text" name="db_connect" style="500px;" value="<?=$db_config['connect'] ?>" placeholder="连接字符串" />
141     <input type="text" name="db_user" style="100px;" value="<?=$db_config['user'] ?>" placeholder="用户名" />
142     <input type="password" name="db_password" style="100px;" value="<?=$db_config['password'] ?>" placeholder="密码"  /><br>
143     <div style="font-size:12px; color:gray;">例如：mysql:host=localhost;port=3306;dbname=test</div>
144     <textarea name="sql" placeholder="SQL语句" style="color:blue; 740px; height:150px; font-size:14px;"><?=$sql ?></textarea>
145     <div>
146         <button type="submit">执行</button>
147         &emsp;
148         <button type="button" onclick="runSql('show tables;'); return false;">所有表</button>
149         <button type="button" onclick="runSql('show table status;'); return false;">表详情</button>
150         <button type="button" onclick="runSql('show full processlist;'); return false;">进程</button>
151         <button type="button" onclick="runSql('show databases;'); return false;">数据库</button>
152         <button type="button" onclick="runSql('show status;'); return false;">状态</button>
153         <button type="button" onclick="runSql('show errors;'); return false;">错误</button>
154         <button type="button" onclick="runSql('show warnings;'); return false;">警告</button>
155         <button type="button" onclick="runSql('show create table table1;'); return false;">表创建</button>
156         <button type="button" onclick="runSql('desc table1;'); return false;">表字段</button>
157         <button type="button" onclick="runSql('show variables;'); return false;">配置</button>
158     </div>
159     </form>
160     <hr>
161     <?php if (!empty($error)) { ?>
162         <pre style="color:red; font-size:14px; font-family:consolas, Courier New;"><?= $error ?></pre>
163     <?php } ?>
164     <?php if (!empty($res)) { ?>
165         <span>执行用时：<?= $res['use_time']; ?>ms</span> 
166         <?php if (is_array($res['data'])) { ?>
167 <style>
168 .table{
169     border-spacing: 0;
170     border-collapse: collapse;
171     border:solid 2px #aaa;
172     background:#fff;
173     font-size:12px;
174 }
175 .table th{
176     font-weight:normal;
177 }
178 .table td, .table th{
179     border:solid 1px #aaa;
180     padding:3px 5px;
181     text-align:center;
182     white-space:nowrap; overflow:hidden; text-overflow:ellipsis;
183 }
184 .table thead{border-bottom:solid 2px #aaa; background:#ddd;}
185 .table tbody tr:hover{background:#eff;}
186 </style>
187 <span>共<?= count($res['data']); ?>行</span>
188 <?php if (count($res['data']) > 0) { ?>
189 <span>共<?= count(array_keys($res['data'][0])); ?>列</span>
190 <table class="table">
191 <thead>
192  <tr>
193      <th>#</th>
194     <?php foreach (array_keys($res['data'][0]) as $r) { ?>
195     <th><?= f($r); ?></th>
196     <?php } ?>
197  </tr>
198 </thead>
199 <tbody>
200     <?php 
201     $i = 0;
202     foreach ($res['data'] as $r) { 
203         $i++;
204         if ($i > $row_limit)
205         {
206             break;
207         }
208     ?>
209     <tr>
210         <td><?= $i; ?></td>
211         <?php foreach ($r as $rr) { ?>
212         <td><?= f($rr); ?></td>
213         <?php } ?>
214     </tr>
215     <?php } ?>
216 </tbody>
217 </table>
218 <?php } ?>
219         <?php }else{ ?>
220         <span>影响行数：<?= $res['data']; ?></span>
221         <?php } ?>
222     <?php } ?>
223 
224 <script>
225 function runSql(sql)
226 {
227     $('textarea[name=sql]').val(sql);
228     $('#frmQuery').submit();
229 }
230 
231 $(function(){
232     $('#frmQuery').submit(function(){
233         if (window.localStorage)
234         {
235             window.localStorage.setItem('db_connect', $('input[name=db_connect]').val());
236             window.localStorage.setItem('db_user', $('input[name=db_user]').val());
237             window.localStorage.setItem('db_password', $('input[name=db_password]').val());
238             window.localStorage.setItem('db_sql', $('textarea[name=sql]').val());
239         }
240     });
241 
242     if (window.localStorage)
243     {
244         var v = null;
245         v = window.localStorage.getItem('db_connect');
246         $('input[name=db_connect]').val(v||'');
247         v = window.localStorage.getItem('db_user');
248         $('input[name=db_user]').val(v||'');
249         v = window.localStorage.getItem('db_password');
250         $('input[name=db_password]').val(v||'');
251         v = window.localStorage.getItem('db_sql');
252         $('textarea[name=sql]').val(v||'');
253     }
254 });
255 </script>
256 </body>
257 </html>
相关阅读:
java获取程序执行时间
 自己不去努力还有谁能帮你
 错误: 找不到或无法加载主类的解决办法
 不要迷信红黑树哈希是一切
 nancy的诊断2
nancy中的诊断功能面板1
ironpython 2.75 在c#中的使用
 sqlserver2008创建数据库报 Cannot read property is filestream 此属性不可用于sql server 7.0 解决
 结巴net 分词配置文件路径，在网站中的出现问题的解决
 akka 练手原来第一次是原封不动的返回传出去的参数
原文地址：https://www.cnblogs.com/zjfree/p/9454552.html