PHP实现DataGrid

最近想做一个通过PHP实现DataGrid功能的东西，这样可以直接修改数据库中表的内容，而不用开发【新增数据页面】，【编辑页面】，于是乎在网上找了找，类似的东西也有几个，开源的、付费的都有，不过基本都是基于MySQL。由于需要连接Oracle所以从二次开发和页

       最近想做一个通过PHP实现DataGrid功能的东西，这样可以直接修改数据库中表的内容，而不用开发【新增数据页面】，【编辑页面】，于是乎在网上找了找，类似的东西也有几个，开源的、付费的都有，不过基本都是基于MySQL。由于需要连接Oracle所以从二次开发和页面样式来说个人觉得phpMyDataGrid还是比较好上手。本篇首先介绍基于MySQL的使用方法，再简单介绍对于Oracle连接（基于sqlrelay）的二次开发。

1. 创建测试数据库和表

<span>create database </span>`guru`<span>;</span>

<span>USE </span>`guru`<span>;</span>

<span>CREATE TABLE </span>`employees` <span>(
      </span>`id` <span>int</span><span>(</span>6<span>) NOT NULL </span>auto_increment<span>,
      </span>`name` <span>char</span><span>(</span>20<span>) </span><span>default </span><span>NULL,
      </span>`lastname` <span>char</span><span>(</span>20<span>) </span><span>default </span><span>NULL,
      </span>`salary` <span>float default </span><span>NULL,
      </span>`age` <span>int</span><span>(</span>2<span>) </span><span>default </span><span>NULL,
      </span>`afiliation` <span>date default </span><span>NULL,
      </span>`status` <span>int</span><span>(</span>1<span>) </span><span>default </span><span>NULL,
      </span>`active` <span>tinyint</span><span>(</span>1<span>) </span><span>default </span><span>NULL,
      </span>`workeddays` <span>int</span><span>(</span>2<span>) </span><span>default </span><span>NULL,
      </span>`photo` <span>char</span><span>(</span>30<span>) </span><span>default </span><span>NULL,
      </span><span>PRIMARY KEY  </span><span>(</span>`id`<span>)
)</span>

<span>insert into </span>`employees`
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>) 
    </span><span>values </span><span>(</span>1<span>, </span><span>'Ana'</span><span>, </span><span>'Trujillo'</span><span>,</span>2000<span>,</span>45<span>, </span><span>'2005-05-13'</span><span>,</span>1<span>,</span>1<span>,</span>10<span>, </span><span>'1.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>2<span>, </span><span>'Jennifer'</span><span>, </span><span>'Aniston'</span><span>,</span>3500<span>,</span>23<span>, </span><span>'2004-10-22'</span><span>,</span>1<span>,</span>0<span>,</span>0<span>, </span><span>'2.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>3<span>, </span><span>'Michael'</span><span>, </span><span>'Norman'</span><span>,</span>1200<span>,</span>19<span>, </span><span>'2007-01-10'</span><span>,</span>1<span>,</span>1<span>,</span>5<span>, </span><span>'3.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>4<span>, </span><span>'Vanessa'</span><span>, </span><span>'Black'</span><span>,</span>6500<span>,</span>31<span>, </span><span>'2000-11-05'</span><span>,</span>1<span>,</span>1<span>,</span>30<span>, </span><span>'4.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>5<span>, </span><span>'Michael'</span><span>, </span><span>'Strauss'</span><span>,</span>3200<span>,</span>45<span>, </span><span>'2006-10-21'</span><span>,</span>2<span>,</span>0<span>,</span>22<span>, </span><span>'5.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>6<span>, </span><span>'William'</span><span>, </span><span>'Brown'</span><span>,</span>2300<span>,</span>21<span>, </span><span>'2001-03-10'</span><span>,</span>3<span>,</span>1<span>,</span>10<span>, </span><span>'6.jpg'</span><span>);
</span><span>insert into </span>`employees` 
    <span>(</span>`id`<span>,</span>`name`<span>,</span>`lastname`<span>,</span>`salary`<span>,</span>`age`<span>,</span>`afiliation`<span>,</span>`status`<span>,</span>`active`<span>,</span>`workeddays`<span>,</span>`photo`<span>)
    </span><span>values </span><span>(</span>7<span>, </span><span>'Lucca'</span><span>, </span><span>'Normany'</span><span>,</span>2800<span>,</span>36<span>, </span><span>'2006-10-02'</span><span>,</span>3<span>,</span>1<span>,</span>20<span>, </span><span>'7.jpg'</span><span>);</span>

 

2. PHP程序介绍

       phpMyDataGrid主要是通过phpmydatagrid.class.php，dgscripts.js来实现的，总共加起来不到100kB，又是一个小巧的软件。对于这两个文件就不多讲了，感兴趣的同学可以“打包带走”回去慢慢品。主要介绍该软件的使用方法，即实例datagrid_for_mysql.php。先看一下页面示意图：

程序讲解：

<span><?php include </span><span>(</span><span>"phpmydatagrid.class.php"</span><span>)</span>;
<span>$objGrid </span><span>= </span><span>new </span>datagrid;

<span>$objGrid</span><span>-></span><span>(</span><span>true</span><span>)</span>;  
<span>$objGrid</span><span>-></span>friendlyHTML<span>()</span>;  
<span>$objGrid</span><span>-></span>methodForm<span>(</span><span>"get"</span><span>)</span>; 
<span>//连接数据库
</span><span>$objGrid</span><span>-></span>conectadb<span>(</span><span>"127.0.0.1"</span><span>, </span><span>"root"</span><span>, </span><span>"root"</span><span>, </span><span>"guru"</span><span>)</span>;<br><span>//加密字符串
</span><span>$objGrid</span><span>-></span>salt<span>(</span><span>"Myc0defor5tr0ng3r-Pro3EctiOn"</span><span>)</span>;
<span>$objGrid</span><span>-></span>language<span>(</span><span>"en"</span><span>)</span>;
<span>//最后一列显示的功能键，从左向右功能为“新增键”、“编辑键”、“删除键”、“浏览键”。
</span><span>$objGrid</span><span>-></span>buttons<span>(</span><span>true</span><span>,</span><span>true</span><span>,</span><span>true</span><span>,</span><span>true</span><span>)</span>;
<span>//修改数值时产生的Form名称
</span><span>$objGrid</span><span>-></span>form<span>(</span><span>'employee'</span><span>, </span><span>true</span><span>)</span>;
<span>//可检索列名
</span><span>$objGrid</span><span>-></span>searchby<span>(</span><span>"name,lastname"</span><span>)</span>;
<span>//需要读取的表
</span><span>$objGrid</span><span>-></span>tabla<span>(</span><span>"employees"</span><span>)</span>;
<span>//索引值用于修改数据
</span><span>$objGrid</span><span>-></span>keyfield<span>(</span><span>"id"</span><span>)</span>;
<span>//分页显示行数
</span><span>$objGrid</span><span>-></span>datarows<span>(</span><span>20</span><span>)</span>;
<span>//默认排序方式
</span><span>$objGrid</span><span>-></span>orderby<span>(</span><span>"name"</span><span>, </span><span>"ASC"</span><span>)</span>;
<span>//显示列设置，相关设置可参考phpmydatagrid.class.php
</span><span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"id"</span><span>, </span><span>"ID Employee"</span><span>, </span><span>5</span><span>, </span><span>5</span><span>, </span><span>1</span><span>, </span><span>"50"</span><span>, </span><span>"center"</span><span>, </span><span>"integer"</span><span>)</span>;
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"name"</span><span>, </span><span>"Name"</span><span>, </span><span>30</span><span>, </span><span>30</span><span>, </span><span>0</span><span>, </span><span>"150"</span><span>, </span><span>"left"</span><span>)</span>;
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"lastname"</span><span>, </span><span>"Last name"</span><span>, </span><span>30</span><span>, </span><span>30</span><span>, </span><span>0</span><span>, </span><span>"150"</span><span>, </span><span>"left"</span><span>)</span>;
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"age"</span><span>, </span><span>"Age"</span><span>, </span><span>5</span><span>, </span><span>5</span><span>, </span><span>0</span><span>, </span><span>"50"</span><span>, </span><span>"right"</span><span>)</span>;<br><span>//自定义日期格式
</span><span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"afiliation"</span><span>, </span><span>"Afiliation Date"</span><span>, </span><span>10</span><span>, </span><span>10</span><span>, </span><span>0</span><span>, </span><span>"100"</span><span>, </span><span>"center"</span><span>, </span><span>"date:dmy:/"</span><span>)</span>;<br><span>//编辑时可以自定义为<select>模式</select></span>
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"status"</span><span>, </span><span>"Status"</span><span>, </span><span>5</span><span>, </span><span>5</span><span>, </span><span>0</span><span>, </span><span>"60"</span><span>, </span><span>"left"</span><span>, </span><span>"select:1_Single:2_Married:3_Divorced"</span><span>)</span>;
<span><span>//编辑时可以自定义为<checkbox>模式</checkbox></span>
</span><span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"active"</span><span>, </span><span>"Active"</span><span>, </span><span>2</span><span>, </span><span>2</span><span>, </span><span>0</span><span>,</span><span>"50"</span><span>, </span><span>"center"</span><span>, </span><span>"check:No:Yes"</span><span>)</span>;<br><span>//自定义货币显示形式</span>
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"salary"</span><span>, </span><span>"Salary"</span><span>, </span><span>10</span><span>, </span><span>10</span><span>, </span><span>0</span><span>, </span><span>"90"</span><span>, </span><span>"right"</span><span>, </span><span>"money:€"</span><span>)</span>;<br><span>//将数据以柱状图显示</span>
<span>$objGrid</span><span>-></span>FormatColumn<span>(</span><span>"workeddays"</span><span>, </span><span>"Work days"</span><span>, </span><span>5</span><span>, </span><span>2</span><span>, </span><span>0</span><span>, </span><span>"50"</span><span>, </span><span>"right"</span><span>, </span><span>"chart:percent:val:31"</span><span>)</span>;
<span>$objGrid</span><span>-></span>checkable<span>()</span>;
<span>$objGrid</span><span>-></span>setHeader<span>()</span>;
<span>$objGrid</span><span>-></span>ajax<span>(</span><span>'silent'</span><span>)</span>;
<span>echo </span><span>'
      <title>PHPDataGrid</title>
      <div align="center">
<br>';
<span>//生成DataGrid
</span><span>$objGrid</span><span>-></span>grid<span>()</span>;
<span>echo </span><span>'</span>
</div>'</span>;<br><span>//关闭数据库连接</span>
<span>$objGrid</span><span>-></span>desconectar<span>()</span>;
<span>?></span></span>

3. 基于Oracle简介

对于Oracle的读取主要是把phpmydatagrid.class.php中与MySQL连接的函数修改为Oracle，本篇是通过sqlrelay（可参考http://sqlrelay.sourceforge.net/）进行的Oracle连接，当然也可以使用PHP自带的OCI8模块（效率有些低），修改后另存为phporadatagrid.class.php即可在其他程序（datagrid_for_oracle.php）中调用。以上涉及的所有程序均可在压缩包中找到。
希望对大家有用！

4. 源程序下载