php - Show MySQL table and convert it into DataTables without refreshing the page
Get the solution ↓↓↓
I am trying to load a MySQL table and store it into an HTML table to then convert it into Datatables. However, I have some troubles pointing on the table I fetched with PHP.
I have already read these two pages (page 1, page 2), but it does not show how to point on the table.
So here's my code :
HTML
<!DOCTYPE html>
<!-- Connexion page. It is the first page og the web app -->
<html>
<head>
<meta charset="utf-8" />
<title>Temporis V Crafting List</title>
<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css"/>
<!-- jQuery library -->
<script src="js/jquery-3.6.0.min.js"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<!-- JS script -->
<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<table id="memListTable" class="display" style="width:100%"></table>
</body>
</html>
Nothing special here, I just store the table intotable#memListTable.
JavaScript
function refresh_table() {
// AJAX code to execute query and get back to same page with table content without reloading the page.
$.ajax({
type: "POST",
url: "load_table.php",
cache: false,
success: function(html) {
// alert(dataString);
console.log(html);
$("#memListTable").html(html);
}
});
};
$(document).ready(function() {
refresh_table();
var table = $('#memListTable').DataTable({
"columns": [
{"data": "id"},
{"data": "Item"},
{"data": "Carte 1"},
{"data": "Carte 2"},
{"data": "Carte 3"},
{"data": "Carte 4"},
{"data": "Carte 5"}
]
});
} );
Here I manually set the thead with DataTables columns.data options because I cannot seem to point on the full table.
load_table.php
<?php
$link = mysqli_connect("localhost", "root", "", "temporis") or die("Echec de connexion Г la base");
$requette = "SELECT * FROM crafts";
$resultat = mysqli_query($link, $requette);
$rs = mysqli_fetch_array($resultat);
?>
<thead>
<tr>
<th>id</th>
<th>Item</th>
<th>Carte 1</th>
<th>Carte 2</th>
<th>Carte 3</th>
<th>Carte 4</th>
<th>Carte 5</th>
</tr>
</thead>
<tbody>
<?php do { ?>
<tr>
<td><?php echo $rs['id']; ?></td>
<td><?php echo $rs['Item']; ?></td>
<td><?php echo $rs['Carte 1']; ?></td>
<td><?php echo $rs['Carte 2']; ?></td>
<td><?php echo $rs['Carte 3']; ?></td>
<td><?php echo $rs['Carte 4']; ?></td>
<td><?php echo $rs['Carte 5']; ?></td>
</tr>
<?php }while($rs = mysqli_fetch_array($resultat)); ?>
</tbody>
<tfoot>
<tr>
<th>id</th>
<th>Item</th>
<th>Carte 1</th>
<th>Carte 2</th>
<th>Carte 3</th>
<th>Carte 4</th>
<th>Carte 5</th>
</tr>
</tfoot>
<?php
mysqli_close($link);
?>
From teh code above I get the following web page :
I cannot filter the table, cannot order the columns, cannot interact at all with the DataTables. It works astable#memeListTable is empty when converting the html table into a DataTables.
I hope you guys will help me. Thanks in advance.
Answer
Solution:
Here is how you can create dynamic table with php and mysql with datatables:
html:
<!DOCTYPE html>
<!-- Connexion page. It is the first page og the web app -->
<html>
<head>
<meta charset="utf-8" />
<title>Temporis V Crafting List</title>
<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css"/>
<!-- jQuery library -->
<script src="js/jquery-3.6.0.min.js"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<!-- JS script -->
<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<table id="memListTable" class="display" style="width:100%">
<thead>
<tr>
<th>id</th>
<th>Item</th>
<th>Carte 1</th>
<th>Carte 2</th>
<th>Carte 3</th>
<th>Carte 4</th>
<th>Carte 5</th>
</tr>
</thead>
<tbody></tbody>
</table>
</body>
</html>
Table script:
var memListTable = $('#memListTable').DataTable({
ajax: {
url: 'load_table.php',
data: function() {
//for sending extra params along with the URL above
// if needed
return {sample: "data"};
},
dataSrc: function(data) {
return data || [];
}
},
processing: true, // this will show loading whenever table reloads or any actions heppens with the table
"columns": [
{"data": "id"},
{"data": "Item"},
{"data": "Carte 1"},
{"data": "Carte 2"},
{"data": "Carte 3"},
{"data": "Carte 4"},
{"data": "Carte 5"}
]
});
to refresh the table:
$('button').on('click', function(e) {
e.preventDefault(); // prevents default action
memListTable.ajax.reload(null, false); // this will reload the table everything user submits the form
});
In load_table.php :
$json = [];
var $sql = "SELECT * FROM crafts";
if ($result = $link->query($sql)) {
foreach ($result AS $key => $row) {
$json[] = $row;
}
} else {
$json['status'] = 'error';
$json['message'] = 'Unexpected error';
$json['error'] = $conn->error;
}
header('Content-type: application/json');
echo json_encode($json, JSON_PRETTY_PRINT);
this is how easily you can create dynamic table in php and datables
Here is a link where I wrote an article about how to do it with daterange and others: https://sarifulislam.com/add-date-range-system-in-datatables/
Answer
Solution:
I found a solution myself. The thing is to wait for the table to be filled with the content and then apply the DataTable function. I do it like this :
JavaScript
var table;
function refresh_table() {
// AJAX code to execute query and get back to same page with table content without reloading the page.
$.ajax({
type: "POST",
url: "load_table.php",
cache: false,
success: function(html) {
$("#memListTable").html(html);
table = $('#memListTable').DataTable();
}
});
};
$(document).ready(function() {
refresh_table();
} );
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: php undefined array key
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.