Requête de pagination dans un Datatable
Bonjour à toutes et tous,
J'essaie de récupérer en ajax des données issues d'une base MySql pour remplir une datatable.
Tout fonctionne plutôt pas mal sauf la pagination. Pour les 4 premières pages, pas de soucis, mais dès que je veux afficher la 5ème, ca ne fonctionne plus alors que les paramètres passés semble corrects.
Voici le code html/javascript:
Code:
1
2
3
4
5
6
7
8
9
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
| <!DOCTYPE html>
<html>
<title>Consultation des comptes Twitter collectés au titre du dépôt légal du Web</title>
<link rel="stylesheet" type="text/css" href="datatable/DataTables-1.10.18/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="/datatable/Buttons-1.5.6/css/buttons.dataTables.css">
<script type="text/javascript" language="javascript" src="datatable/DataTables-1.10.18/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="datatable/DataTables-1.10.18/js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript" src="/datatable/Buttons-1.5.6/js/buttons.colVis.min.js"></script>
<script type="text/javascript" language="javascript" src="/datatable/Buttons-1.5.6/js/buttons.html5.min.js"></script>
<script type="text/javascript" language="javascript" src="/datatable/Buttons-1.5.6/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" charset="utf-8_encode">
$(document).ready(function(){
var dataTable = $('#t_twitter').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
//'searching': false, // Remove default Search Control
'ajax': {
'url':'get_T_TWITTER_data_light.php',
'data': function(data){
// Read values
var i_Mention = $('#searchByMention').val();
var i_Id = $('#searchById').val();
var i_Name = $('#searchByName').val();
var i_Localisation = $('#searchByLocalisation').val();
// Append to data
data.searchByMention = i_Mention;
data.searchById = i_Id;
data.searchByName = i_Name;
data.searchByLocalisation = i_Localisation;
}
},
'columns': [
{ data: 'TWITTER_USER_SCREEN_NAME' },
{ data: 'TWITTER_USER_ID' },
{ data: 'TWITTER_USER_NAME' },
{ data: 'TWITTER_USER_LOCATION' }
]
});
$('#searchByMention').keyup(function(){
dataTable.draw();
});
$('#searchById').change(function(){
dataTable.draw();
});
$('#searchByName').change(function(){
dataTable.draw();
});
$('#searchByLocalisation').change(function(){
dataTable.draw();
});
});
</script>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
body {
font-family:Arial;
font-size:smaller;
color: black;
background-color: #FFFFFF
}
</style>
<BR>
<BR>
<BR>
<center><h1><font>Consultation des comptes Twitter collectés au titre du dépôt légal du Web</font></h1></center>
<BR>
<BR>
<BR>
<table>
<tr>
<td><input type='text' id='searchByMention' placeholder='Filtrer par Mention'> </td>
<td><input type='text' id='searchById' placeholder='Filtrer par Id'> </td>
<td><input type='text' id='searchByName' placeholder='Filtrer par Nom '> </td>
<td><input type='text' id='searchByLocalisation' placeholder='Filtrer par Localisation '> </td>
</td>
</tr>
</table>
<div class= "container">
<table id="t_twitter" class="display" style="width:100%">
<thead>
<tr>
<th><b><u><font color="black">Mention du compte</font></u></b></th>
<th><b><u><font color="black">Id du compte</font></u></b></th>
<th><b><u><font color="black">Nom du compte</font></u></b></th>
<th><b><u><font color="black">Localisation du compte</font></u></b></th>
</tr>
</thead>
<tfoot>
<tr>
<th>Mention du compte</th>
<th>Id du compte</th>
<th>Nom du compte</th>
<th>Localisation du compte</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html> |
Et la partie Ajax/php:
Code:
1
2
3
4
5
6
7
8
9
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
| <?php
include 'config.php';
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
## Custom Field value
$searchByMention = $_POST['searchByMention'];
$searchById = $_POST['searchById'];
$searchByName = $_POST['searchByName'];
$searchByLocalisation = $_POST['searchByLocalisation'];
## Search
$searchQuery = " ";
if($searchByLocalisation != ''){
$searchQuery .= " and (TWITTER_USER_SCREEN_NAME like '%".$searchByLocalisation."%' ) ";
}
if($searchById != ''){
$searchQuery .= " and (TWITTER_USER_ID like '%".$searchById."%' ) ";
}
if($searchByName != ''){
$searchQuery .= " and (TWITTER_USER_NAME like '%".$searchByName."%' ) ";
}
if($searchByLocalisation != ''){
$searchQuery .= " and (TWITTER_USER_LOCATION like '%".$searchByLocalisation."%' ) ";
}
if($searchValue != ''){
$searchQuery .= " and ((TWITTER_USER_SCREEN_NAME like '%".$searchValue."%') ";
$searchQuery .= " or ( TWITTER_USER_ID like '%".$searchValue."%') ";
$searchQuery .= " or ( TWITTER_USER_NAME like '%".$searchValue."%') ";
$searchQuery .= " or ( TWITTER_USER_LOCATION like '%".$searchValue."%')) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from T_TWITTER");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from T_TWITTER WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from T_TWITTER WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"TWITTER_USER_SCREEN_NAME"=>$row['TWITTER_USER_SCREEN_NAME'],
"TWITTER_USER_ID"=>$row['TWITTER_USER_ID'],
"TWITTER_USER_NAME"=>$row['TWITTER_USER_NAME'],
"TWITTER_USER_LOCATION"=>$row['TWITTER_USER_LOCATION']
);
}
## Response
$response = array(
"empQuery" => $empQuery,
"draw" => $draw,
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response); |
D'avance merci.
