Datagrid is a major requirement in any Database application. Not many free datagrids are available for PHP. Those that are available lack in features. In this series we are going to use
Jquery DataTable along with PHP and MySql to create a Sortable, Filterable, Customizable datagrid.
Simple MySql Recordset Display
Step 1: First Lets Add All the Required Libraries to the Page.
<script src="https://code.jquery.com/jquery-1.12.4.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" rel="stylesheet" />
Step 2: Get the data from the database and display it in an Html Table
|
1
2
3
|
<table class="data-grid">
<thead>
<tr>
|
|
|
<table class="data-grid">
<thead>
<tr>
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
<?php
define("HOSTNAME","localhost");
define("USERNAME","root");
define("PASS","");
define("DB","world");
$connection=new mysqli(HOSTNAME,USERNAME,PASS,DB);
if(!$connection){
echo "Sorry No Connection";
exit;
}
$results=$connection->query("SHOW COLUMNS from city");
while($headers=mysqli_fetch_array($results,MYSQLI_NUM)){
echo "<th>".$headers[0]."</th>";
}
echo "</tr>";
echo "</thead><tbody>";
$output=$connection->query("SELECT * from city");
while($rs=mysqli_fetch_array($output,MYSQLI_NUM)){
echo "<tr>";
for($i=0;$i<count($rs);$i++){
echo "<td>".$rs[$i]."</td>";
}
echo "</tr>";
}
?>
</tbody>
</tr>
</table>
|
|
|
<?php
// Define Database Connection Information
define("HOSTNAME","localhost");
define("USERNAME","root");
define("PASS","");
define("DB","world");
// Make a Connection
$connection=new mysqli(HOSTNAME,USERNAME,PASS,DB);
if(!$connection){
echo "Sorry No Connection";
exit;
}
// Get Column Names of the Table
$results=$connection->query("SHOW COLUMNS from city");
// Print the Table
while($headers=mysqli_fetch_array($results,MYSQLI_NUM)){
echo "<th>".$headers[0]."</th>";
}
echo "</tr>";
echo "</thead><tbody>";
// Get Table Data from Database
$output=$connection->query("SELECT * from city");
//Output the Data
while($rs=mysqli_fetch_array($output,MYSQLI_NUM)){
echo "<tr>";
for($i=0;$i<count($rs);$i++){
echo "<td>".$rs[$i]."</td>";
}
echo "</tr>";
}
?>
</tbody>
</tr>
</table>
Step 3: Finally initialize the datatable .
4
|
1
2
3
4
5
|
<script type="text/javascript">
$(document).ready(function(e) {
$('.data-grid').dataTable();
});
</script>
|
|
|
<script type="text/javascript">
$(document).ready(function(e) {
$('.data-grid').dataTable();
});
</script>
Save the File in your virtual directory. Load it in your browser and you should see
The Source File can be downloaded from
here.
In the next installment we will look into loading data into the Table using Ajax.
