Using Jquery DataGrid with PHP and MySql as a Datagrid Part 1

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. 

?

1 2 3	<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" />

<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 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>

<?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.