DataTables：从MySQL获取数据

Posted on: 2019年2月14日 2019年2月14日
Categories: MySQL
Tags: DataTables, MySQL, php, 善用佳软, 数据库, 数据源, 李一桐

前提：
DataTables的环境的搭建不在描述，前面的文档中都已经写了。

本文主要有以下部分：
1. PHP代码的编写：两个PHP文件
2. 数据库的数据表的准备：MySQL

先看看数据库的部分：

[root@itop ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 8.0.13-3 Percona Server (GPL), Release 3, Revision a920dd6

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
| id | contact_name                 | org_name              | email                | phone           | notify |
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
|  1 | My last name                 | My Company/Department | my.email@foo.org     | +00 000 000 000 | yes    |
|  2 | Picasso                      | Demo                  | pablo@demo.com       |                 | yes    |
|  3 | Dali                         | Demo                  | dali@demo.com        |                 | yes    |
|  4 | Rousseau                     | IT Department         | rousseau@it.com      |                 | yes    |
|  5 | Delacroix                    | Demo                  | delacroix@demo.com   |                 | yes    |
|  6 | Monet                        | Demo                  | monet@demo.com       |                 | yes    |
|  7 | Hugo                         | IT Department         | hugo@it.com          |                 | yes    |
|  8 | Kahlo                        | Demo                  | frida.kahlo@demo.com |                 | yes    |
|  9 | Verne                        | IT Department         | vernes@it.com        |                 | yes    |
| 10 | Flaubert                     | IT Department         | flaubert@it.com      |                 | yes    |
| 11 | Cocteau                      | IT Department         | cocteau@it.com       |                 | yes    |
| 12 | Gavalda                      | IT Department         | gavalda@it.com       |                 | yes    |
| 13 | Duras                        | IT Department         | duras@it.com         |                 | yes    |
| 14 | Sartre                       | IT Department         | sartre@it.com        |                 | yes    |
| 15 | Christie                     | Demo                  | christie@demo.com    |                 | yes    |
| 16 | Vian                         | IT Department         | vian@it.com          |                 | yes    |
| 17 | Parker                       | IT Department         | parker@it.com        |                 | yes    |
| 18 | De Luca                      | Demo                  | deluca@demo.com      |                 | yes    |
| 19 | Kubrick                      | IT Department         | kubrick@it.com       |                 | yes    |
| 20 | King                         | IT Department         | king@it.com          |                 | yes    |
| 21 | de Beauvoir                  | IT Department         | simone@it.com        |                 | yes    |
| 22 | Hardware support             | IT Department         | hw@test.com          |                 | yes    |
| 23 | Helpdesk                     | IT Department         |                      |                 | yes    |
| 24 | Network support              | IT Department         | nw@test.com          |                 | yes    |
| 25 | System & application support | IT Department         |                      |                 | yes    |
| 26 | 王宝强                       | IT Department         | wangbaoqiang@me.com  |                 | yes    |
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
26 rows in set (0.01 sec)

mysql>

[root@itop ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 62

Server version: 8.0.13-3 Percona Server (GPL), Release 3, Revision a920dd6

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;

+----+------------------------------+-----------------------+----------------------+-----------------+--------+

+----+------------------------------+-----------------------+----------------------+-----------------+--------+

| 1 | My last name | My Company/Department | my.email@foo.org | +00 000 000 000 | yes |

| 23 | Helpdesk | IT Department | | | yes |

| 25 | System & application support | IT Department | | | yes |

| 26 | 王宝强 | IT Department | wangbaoqiang@me.com | | yes |

+----+------------------------------+-----------------------+----------------------+-----------------+--------+

26 rows in set (0.01 sec)

mysql>

为了验证中文字符集也能正常显示，所以添加了中文数据。

下面是两个PHP文件

1. PHP访问MySQL的文件：数据源
php2mysql_data.php

[root@itop datatables.adamhuan.com]# pwd
/www/wwwroot/datatables.adamhuan.com
[root@itop datatables.adamhuan.com]# 
[root@itop datatables.adamhuan.com]# ls -ltr
total 7300
-rw-rw-rw- 1 root root 2534893 Oct  6 21:44 datatables.min.js
-rw-rw-rw- 1 root root   62425 Oct  6 21:44 datatables.min.css
-rw-rw-rw- 1 root root 4780700 Oct  6 21:44 datatables.js
-rw-rw-rw- 1 root root   71863 Oct  6 21:44 datatables.css
drwxr-xr-x 3 root root      64 Feb 13 17:17 jQueryUI-1.12.1
drwxr-xr-x 2 root root      56 Feb 13 17:17 jQuery-3.3.1
drwxr-xr-x 2 root root      42 Feb 13 17:17 JSZip-2.5.0
drwxr-xr-x 2 root root      66 Feb 13 17:17 pdfmake-0.1.36
drwxr-xr-x 5 root root      41 Feb 13 17:17 DataTables-1.10.18
drwxr-xr-x 4 root root      27 Feb 13 17:17 AutoFill-2.3.2
drwxr-xr-x 5 root root      38 Feb 13 17:17 Buttons-1.5.4
drwxr-xr-x 4 root root      27 Feb 13 17:17 ColReorder-1.5.0
drwxr-xr-x 4 root root      27 Feb 13 17:17 FixedColumns-3.2.5
drwxr-xr-x 4 root root      27 Feb 13 17:17 FixedHeader-3.1.4
drwxr-xr-x 4 root root      27 Feb 13 17:17 KeyTable-2.5.0
drwxr-xr-x 4 root root      27 Feb 13 17:17 Responsive-2.2.2
drwxr-xr-x 4 root root      27 Feb 13 17:17 RowGroup-1.1.0
drwxr-xr-x 4 root root      27 Feb 13 17:17 RowReorder-1.2.4
drwxr-xr-x 4 root root      27 Feb 13 17:17 Scroller-1.5.0
drwxr-xr-x 4 root root      27 Feb 13 17:17 Select-1.2.6
-rwxr-xr-x 1 www  www       21 Feb 13 22:58 phpinfo.php
-rw-r--r-- 1 root root     930 Feb 13 23:46 php2mysql.php
-rw-r--r-- 1 root root    1947 Feb 14 00:12 php2mysql_data.php
-rw-r--r-- 1 root root    4052 Feb 14 00:35 me.php
[root@itop datatables.adamhuan.com]# 
[root@itop datatables.adamhuan.com]# ls -ltr *.php
-rwxr-xr-x 1 www  www    21 Feb 13 22:58 phpinfo.php
-rw-r--r-- 1 root root  930 Feb 13 23:46 php2mysql.php
-rw-r--r-- 1 root root 1947 Feb 14 00:12 php2mysql_data.php
-rw-r--r-- 1 root root 4052 Feb 14 00:35 me.php
[root@itop datatables.adamhuan.com]# 

[root@itop datatables.adamhuan.com]# cat php2mysql_data.php 
<?php
 
//字符集编码
//header("content-Type: text/html; charset=utf-8");
 
//变量声明
$servername = "127.0.0.1";
$username = "root";
$password = "你的MySQL的口令";
 
//字符集处理：json
function encode_json($str) {
  return urldecode(json_encode(url_encode($str)));
}
 
function url_encode($str) {
  if(is_array($str)) {
    foreach($str as $key=>$value) {
      $str[urlencode($key)] = url_encode($value);
    }
  } else {
    $str = urlencode($str);
  }
 
  return $str;
}
 
//MySQLi extention
//-------------------------------
// 创建连接
//面向对象
//$conn = new mysqli($servername, $username, $password);
 
//面向过程
//$conn = mysqli_connect($servername, $username, $password);
 
// 检测连接
//if ($conn->connect_error) {
//    die("连接失败: " . $conn->connect_error);
//}
//echo "连接成功";
 
//PDO
//----------------------------------------
try {
    $conn = new PDO("mysql:host=$servername;", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"));
    //echo "连接成功<br/>";
    //echo "===================<br/>";
    //echo "";
}
catch(PDOException $e)
{
    echo "连接失败<br/>";
    echo "===================<br/>";
    echo $e->getMessage();
    echo "<br/>";
    echo "";
}
 
//for DataTables Coding
 
//SQL语句
//这个语句，和前面在数据库里查询的一致
$sql = "select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;";
 
//SQL语句执行
$do = $conn->prepare($sql);
$do->execute();
 
//SQL语句，获得结果集
$row = $do->fetchAll(PDO::FETCH_ASSOC);
 
//--> json encoding
//$row_json_encoding = json_encode($row,JSON_UNESCAPED_UNICODE);
 
//输出
//echo $row;
 
//echo _json($row);
//echo $row_json_encoding;
 
$json=encode_json($row);
echo ('{"data":'.$json.'}');
 
//关闭连接
//MYSQLI
//面向对象
//$conn->close();
 
//面向过程
//mysqli_close($conn);
 
//PDO
$conn = null;
 
?>
[root@itop datatables.adamhuan.com]#

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

[root@itop datatables.adamhuan.com]# pwd

/www/wwwroot/datatables.adamhuan.com

[root@itop datatables.adamhuan.com]#

[root@itop datatables.adamhuan.com]# ls -ltr

total 7300

-rw-rw-rw- 1 root root 2534893 Oct 6 21:44 datatables.min.js

-rw-rw-rw- 1 root root 62425 Oct 6 21:44 datatables.min.css

-rw-rw-rw- 1 root root 4780700 Oct 6 21:44 datatables.js

-rw-rw-rw- 1 root root 71863 Oct 6 21:44 datatables.css

drwxr-xr-x 3 root root 64 Feb 13 17:17 jQueryUI-1.12.1

drwxr-xr-x 2 root root 56 Feb 13 17:17 jQuery-3.3.1

drwxr-xr-x 2 root root 42 Feb 13 17:17 JSZip-2.5.0

drwxr-xr-x 2 root root 66 Feb 13 17:17 pdfmake-0.1.36

drwxr-xr-x 5 root root 41 Feb 13 17:17 DataTables-1.10.18

drwxr-xr-x 4 root root 27 Feb 13 17:17 AutoFill-2.3.2

drwxr-xr-x 5 root root 38 Feb 13 17:17 Buttons-1.5.4

drwxr-xr-x 4 root root 27 Feb 13 17:17 ColReorder-1.5.0

drwxr-xr-x 4 root root 27 Feb 13 17:17 FixedColumns-3.2.5

drwxr-xr-x 4 root root 27 Feb 13 17:17 FixedHeader-3.1.4

drwxr-xr-x 4 root root 27 Feb 13 17:17 KeyTable-2.5.0

drwxr-xr-x 4 root root 27 Feb 13 17:17 Responsive-2.2.2

drwxr-xr-x 4 root root 27 Feb 13 17:17 RowGroup-1.1.0

drwxr-xr-x 4 root root 27 Feb 13 17:17 RowReorder-1.2.4

drwxr-xr-x 4 root root 27 Feb 13 17:17 Scroller-1.5.0

drwxr-xr-x 4 root root 27 Feb 13 17:17 Select-1.2.6

-rwxr-xr-x 1 www www 21 Feb 13 22:58 phpinfo.php

-rw-r--r-- 1 root root 930 Feb 13 23:46 php2mysql.php

-rw-r--r-- 1 root root 1947 Feb 14 00:12 php2mysql_data.php

-rw-r--r-- 1 root root 4052 Feb 14 00:35 me.php

[root@itop datatables.adamhuan.com]#

[root@itop datatables.adamhuan.com]# ls -ltr *.php

-rwxr-xr-x 1 www www 21 Feb 13 22:58 phpinfo.php

-rw-r--r-- 1 root root 930 Feb 13 23:46 php2mysql.php

-rw-r--r-- 1 root root 1947 Feb 14 00:12 php2mysql_data.php

-rw-r--r-- 1 root root 4052 Feb 14 00:35 me.php

[root@itop datatables.adamhuan.com]#

[root@itop datatables.adamhuan.com]# cat php2mysql_data.php

<?php

//字符集编码

//header("content-Type: text/html; charset=utf-8");

//变量声明

$servername = "127.0.0.1";

$username = "root";

$password = "你的MySQL的口令";

//字符集处理：json

function encode_json($str) {

return urldecode(json_encode(url_encode($str)));

}

function url_encode($str) {

if(is_array($str)) {

foreach($str as $key=>$value) {

$str[urlencode($key)] = url_encode($value);

}

} else {

$str = urlencode($str);

}

return $str;

}

//MySQLi extention

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

// 创建连接

//面向对象

//$conn = new mysqli($servername, $username, $password);

//面向过程

//$conn = mysqli_connect($servername, $username, $password);

// 检测连接

//if ($conn->connect_error) {

// die("连接失败: " . $conn->connect_error);

//}

//echo "连接成功";

//PDO

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

try {

$conn = new PDO("mysql:host=$servername;", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"));

//echo "连接成功<br/>";

//echo "===================<br/>";

//echo "";

}

catch(PDOException $e)

{

echo "连接失败<br/>";

echo "===================<br/>";

echo $e->getMessage();

echo "<br/>";

echo "";

}

//for DataTables Coding

//SQL语句

//这个语句，和前面在数据库里查询的一致

$sql = "select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;";

//SQL语句执行

$do = $conn->prepare($sql);

$do->execute();

//SQL语句，获得结果集

$row = $do->fetchAll(PDO::FETCH_ASSOC);

//--> json encoding

//$row_json_encoding = json_encode($row,JSON_UNESCAPED_UNICODE);

//输出

//echo $row;

//echo _json($row);

//echo $row_json_encoding;

$json=encode_json($row);

echo ('{"data":'.$json.'}');

//关闭连接

//MYSQLI

//面向对象

//$conn->close();

//面向过程

//mysqli_close($conn);

//PDO

$conn = null;

[root@itop datatables.adamhuan.com]#

2. PHP文件：前端展示页面
me.php

[root@itop datatables.adamhuan.com]# cat me.php
<link rel="stylesheet" type="text/css" href="jQueryUI-1.12.1/themes/base/jquery-ui.css"/>
<link rel="stylesheet" type="text/css" href="DataTables-1.10.18/css/dataTables.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="AutoFill-2.3.2/css/autoFill.jqueryui.min.css"/>
<link rel="stylesheet" type="text/css" href="Buttons-1.5.4/css/buttons.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="ColReorder-1.5.0/css/colReorder.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="FixedColumns-3.2.5/css/fixedColumns.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="FixedHeader-3.1.4/css/fixedHeader.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="KeyTable-2.5.0/css/keyTable.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="Responsive-2.2.2/css/responsive.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="RowGroup-1.1.0/css/rowGroup.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="RowReorder-1.2.4/css/rowReorder.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="Scroller-1.5.0/css/scroller.jqueryui.css"/>
<link rel="stylesheet" type="text/css" href="Select-1.2.6/css/select.jqueryui.css"/>
 
<script type="text/javascript" src="jQuery-3.3.1/jquery-3.3.1.js"></script>
<script type="text/javascript" src="jQueryUI-1.12.1/jquery-ui.js"></script>
<script type="text/javascript" src="JSZip-2.5.0/jszip.js"></script>
<script type="text/javascript" src="pdfmake-0.1.36/pdfmake.js"></script>
<script type="text/javascript" src="pdfmake-0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="DataTables-1.10.18/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="DataTables-1.10.18/js/dataTables.jqueryui.js"></script>
<script type="text/javascript" src="AutoFill-2.3.2/js/dataTables.autoFill.js"></script>
<script type="text/javascript" src="AutoFill-2.3.2/js/autoFill.jqueryui.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/dataTables.buttons.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/buttons.jqueryui.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/buttons.colVis.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/buttons.flash.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/buttons.html5.js"></script>
<script type="text/javascript" src="Buttons-1.5.4/js/buttons.print.js"></script>
<script type="text/javascript" src="ColReorder-1.5.0/js/dataTables.colReorder.js"></script>
<script type="text/javascript" src="FixedColumns-3.2.5/js/dataTables.fixedColumns.js"></script>
<script type="text/javascript" src="FixedHeader-3.1.4/js/dataTables.fixedHeader.js"></script>
<script type="text/javascript" src="KeyTable-2.5.0/js/dataTables.keyTable.js"></script>
<script type="text/javascript" src="Responsive-2.2.2/js/dataTables.responsive.js"></script>
<script type="text/javascript" src="RowGroup-1.1.0/js/dataTables.rowGroup.js"></script>
<script type="text/javascript" src="RowReorder-1.2.4/js/dataTables.rowReorder.js"></script>
<script type="text/javascript" src="Scroller-1.5.0/js/dataTables.scroller.js"></script>
<script type="text/javascript" src="Select-1.2.6/js/dataTables.select.js"></script>

<!-- Table Structure -->

<table id="Hello_World" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>编号</th>
            <th>姓名</th>
            <th>组织</th>
            <th>邮箱</th>
            <th>电话</th>
            <th>是否通知</th>
        </tr>
    </thead>
</table>

<!-- Table Data -->

<script>
$(document).ready( function () {
    $('#Hello_World').DataTable({
       "ajax":"php2mysql_data.php",
       "columns": [
            { "data": "id","title":"编号" },
            { "data": "contact_name","title":"姓名" },
            { "data": "org_name","title":"组织" },
            { "data": "email","title":"邮箱" },
            { "data": "phone",title:"电话" },
            { "data": "notify","title":"是否通知" }
       ]
    });
} );
</script>
[root@itop datatables.adamhuan.com]#

[root@itop datatables.adamhuan.com]# cat me.php

<thead>

<tr>

</tr>

</thead>

</table>

$(document).ready( function () {

$('#Hello_World').DataTable({

"ajax":"php2mysql_data.php",

"columns": [

{ "data": "id","title":"编号" },

{ "data": "contact_name","title":"姓名" },

{ "data": "org_name","title":"组织" },

{ "data": "email","title":"邮箱" },

{ "data": "phone",title:"电话" },

{ "data": "notify","title":"是否通知" }

]

});

} );

</script>

[root@itop datatables.adamhuan.com]#

注意，上面【columns】中的【data】第一个指向的都是前面数据连接PHP中的SQL中的列名，即这里写的都是数据库中的列名。

来看看效果：
1. PHP，数据源的代码

2. PHP，前端展示

————————
在数据库中增加一条数据，看看前端是否能够同步

增加数据：

再查查数据：数据库

mysql> select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
| id | contact_name                 | org_name              | email                | phone           | notify |
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
|  1 | My last name                 | My Company/Department | my.email@foo.org     | +00 000 000 000 | yes    |
|  2 | Picasso                      | Demo                  | pablo@demo.com       |                 | yes    |
|  3 | Dali                         | Demo                  | dali@demo.com        |                 | yes    |
|  4 | Rousseau                     | IT Department         | rousseau@it.com      |                 | yes    |
|  5 | Delacroix                    | Demo                  | delacroix@demo.com   |                 | yes    |
|  6 | Monet                        | Demo                  | monet@demo.com       |                 | yes    |
|  7 | Hugo                         | IT Department         | hugo@it.com          |                 | yes    |
|  8 | Kahlo                        | Demo                  | frida.kahlo@demo.com |                 | yes    |
|  9 | Verne                        | IT Department         | vernes@it.com        |                 | yes    |
| 10 | Flaubert                     | IT Department         | flaubert@it.com      |                 | yes    |
| 11 | Cocteau                      | IT Department         | cocteau@it.com       |                 | yes    |
| 12 | Gavalda                      | IT Department         | gavalda@it.com       |                 | yes    |
| 13 | Duras                        | IT Department         | duras@it.com         |                 | yes    |
| 14 | Sartre                       | IT Department         | sartre@it.com        |                 | yes    |
| 15 | Christie                     | Demo                  | christie@demo.com    |                 | yes    |
| 16 | Vian                         | IT Department         | vian@it.com          |                 | yes    |
| 17 | Parker                       | IT Department         | parker@it.com        |                 | yes    |
| 18 | De Luca                      | Demo                  | deluca@demo.com      |                 | yes    |
| 19 | Kubrick                      | IT Department         | kubrick@it.com       |                 | yes    |
| 20 | King                         | IT Department         | king@it.com          |                 | yes    |
| 21 | de Beauvoir                  | IT Department         | simone@it.com        |                 | yes    |
| 22 | Hardware support             | IT Department         | hw@test.com          |                 | yes    |
| 23 | Helpdesk                     | IT Department         |                      |                 | yes    |
| 24 | Network support              | IT Department         | nw@test.com          |                 | yes    |
| 25 | System & application support | IT Department         |                      |                 | yes    |
| 26 | 王宝强                       | IT Department         | wangbaoqiang@me.com  |                 | yes    |
| 28 | 李一桐                       | Demo                  | liyitong@dreaming.me |                 | yes    |
+----+------------------------------+-----------------------+----------------------+-----------------+--------+
27 rows in set (0.00 sec)

mysql>

mysql> select c.id,c.name as contact_name,o.name as org_name,c.email,c.phone,c.notify from itop_data.contact c,itop_data.organization o where c.org_id = o.id order by c.id;

+----+------------------------------+-----------------------+----------------------+-----------------+--------+