I am currently working on a jQuery datatable, which finally (after some help of the community here) seems to work great, however there is one final issue that remains and that is within the search which seems to be having issues when searching for foreign Characters.
Example1 - onl oad:
Example2 - Searching English Characters client_name:
Until here, everything is fine, the problem happens once I start to look for client_names that are in my system and that are NOT English, for instance I have a couple of client names that have special characters such as Chinese or Thai.
Example3 - Searching Thai Characters client_name:
Main HTML file:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>JQuery Datatable</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#jquery-datatable-ajax-php').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'order': [[5, 'desc']],
'ajax': {
'url':'datatable.php'
},
'columns': [
{ data: 'client_name' },
{ data: 'quote_number' },
{ data: 'project' },
{ data: 'status' },
{ data: 'quote_total', render: $.fn.dataTable.render.number(',', '.', 2,), className: 'dt-body-right'},
{ data: 'id', 'name': 'id', fnCreatedCell: function (nTd, sData, oData, iRow, iCol) {$(nTd).html("<a class='linkboner' href='/quotes/view/" oData.id "?nocache=<?php echo time()?>'>View / Edit</a>");}}
],
'columnDefs': [ {
'targets': 3,
'createdCell': function (td, cellData, rowData, row, col) {
if ( cellData == 'Pending' ) {
$(td).css('color', '#e27522'),
$(td).css('font-weight', 'bold')
}
else if ( cellData == 'Confirmed' ) {
$(td).css('color', 'green'),
$(td).css('font-weight', 'bold')
}
else if ( cellData == 'Accepted' ) {
$(td).css('color', 'lightgreen'),
$(td).css('font-weight', 'bold')
}}
}]
});
} );
</script>
</head>
<body>
<div >
<h2 style="margin-bottom: 30px;">jQuery Datatable</h2>
<table id="jquery-datatable-ajax-php" style="width:100%">
<thead>
<tr>
<th>Client</th>
<th>Quote Number</th>
<th>Project</th>
<th>Status</th>
<th>Amount</th>
<th data-orderable="false">Action</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
And here the datatable.php file:
<?php
include 'connection.php';
$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
$searchArray = array();
// Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " AND (quote_number LIKE :quote_number OR client_name LIKE :client_name OR project LIKE :project ) ";
$searchArray = array(
'quote_number'=>"%$searchValue%",
'client_name'=>"%$searchValue%",
'project'=>"%$searchValue%"
);
}
// Total number of records without filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes JOIN clients ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];
// Total number of records with filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM quotes JOIN clients WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];
// Fetch records
$stmt = $conn->prepare("SELECT quotes.*, clients.client_name FROM quotes INNER JOIN clients ON quotes.client_id = clients.id WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");
// Bind values
foreach ($searchArray as $key=>$search) {
$stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}
$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();
$data = array();
foreach ($empRecords as $row) {
$data[] = array(
"id"=>$row['id'],
"client_name"=>$row['client_name'],
"quote_number"=>$row['quote_number'],
"project"=>$row['project'],
"status"=>$row['status'],
"quote_total"=>$row['quote_total']
);
}
// Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
?>
Everything renders fine and the search is working great, but as soon as I enter foreign characters in the search I am ending up with the above mentioned error. Some expert help would be greatly appreciated, thank you.
CodePudding user response：
The db table quotes must use the appropriate COLLATION in order for your search to return right results when unicode characters are given in WHERE clause.
See this SO answer on using the COLLATE setting when you create your table schema.
Also, you could do a binary comparison, like so,
SELECT * from quotes WHERE client_name LIKE BINARY '%<search_val>%'