multiple issues with sorting column

smartb66

Hi, when I load page ..datatables fetches database from MYSQL and display as usual.. BUT....

Problem 1: When I click on "Name" header to sort it , it goes blank and doesn't show any data in any column without any error displaying "No data available in table".. i.e empty datatables ....

response :-
{"draw":2,"recordsTotal":0,"recordsFiltered":3,"data":[]}

Problem 2: Though when click on other column headers database is fetched correctly but they are not sorting correctly in ASC and DESC when clicked on headers ..data changes but not in correct order.. NONE OF COLUMN SORTING working Correctly...
response for SORTING EMAIL IDs:-

DESC Email Column:-

{draw: 2, recordsTotal: 3, recordsFiltered: 3,…}
data
:
0
:
["Aman Sharma", "8950376887", "praa@gmail.com", "Hongkong", "Insurance",…]
1
:
["Amar", "5454545477", "amar@gmail.com", "Delhi", "Insurance",…]
2
:
["Sandeep", "8950388989", "sandy@gmail.com", " Paris", "Investment",…]
draw
:
2
recordsFiltered
:
3
recordsTotal
:
3

ASC EMAIL Column :

{draw: 3, recordsTotal: 3, recordsFiltered: 3,…}

data

0
:
["Sandeep", "8950388989", "sandy@gmail.com", " Paris", "Investment",…]
1
:
["Amar", "5454545477", "amar@gmail.com", "Delhi", "Insurance",…]
2
:
["Aman Sharma", "8950376887", "praa@gmail.com", "Hongkong", "Insurance",…]
draw
:
3
recordsFiltered
:
3
recordsTotal
:
3

My code to fetch data fetch.php:-

<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM client ";
if(isset($_POST["search"]["value"]))
{
$query .= 'WHERE name LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR address LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR phone LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR email LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR query LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{

$sub_array = array();

$sub_array[] = $row["name"];
$sub_array[] = $row["phone"];
$sub_array[] = $row["email"];
$sub_array[] = $row["address"];
$sub_array[] = $row["query"];
$sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button> <button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';;

$data[] = $sub_array;

}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $filtered_rows,
"recordsFiltered" => get_total_all_records(),
"data" => $data
);
echo json_encode($output);

<?php > ?>

function.php :-

<?php

function get_total_all_records()
{
include('db.php');
$statement = $connection->prepare("SELECT * FROM client");
$statement->execute();
$result = $statement->fetchAll();
return $statement->rowCount();
}

<?php > ?>

allan

Problem 1: When I click on "Name" header to sort it , it goes blank and doesn't show any data in any column without any error displaying "No data available in table".. i.e empty datatables ....

response :-
{"draw":2,"recordsTotal":0,"recordsFiltered":3,"data":[]}

Looks like a problem with your server-side script. I'd suggest outputting the SQL statement it is generating so you can debug that.

Problem 2: Though when click on other column headers database is fetched correctly but they are not sorting correctly in ASC and DESC when clicked on headers

You are using server-side processing, so again the problem is in your server-side script. The SQL statement being used will help you debug this part as well.

Allan

smartb66

So after outputting the SQL statement what I understood is.. when I click on first header "NAME" , The corresponding SQL statement is..

SELECT * FROM client WHERE name LIKE "%%" OR phone LIKE "%%" OR email LIKE "%%" OR address LIKE "%%" OR query LIKE "%%" ORDER BY 0 asc

When i executed same in Phpmyadmin I got ERROR:

1054 - Unknown column '0' in 'order clause'

then i changed column from 1 to 4 it mysql executed it and displayed correct order..

Then I noticed that datatables index 0 is 1 in mysql index and so on...
**
how to fix it??? **
**
THIS IS MY SCRIPT part :-**

if(isset($_POST["order"]))
{

$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';

}
else
{
$query .= 'ORDER BY id DESC ';
}

MYSQL table structure :--

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name varchar(150) NO NULL
phone varchar(150) NO NULL
email varchar(255) NO NULL
address varchar(255) NO NULL
query varchar(150) NO NULL

allan

$_POST['order']['0']['column']

That will just give you a column index. That is where the 0 is coming from.

You need to convert that into a column name that the SQL server will understand. Normally you would have a map of the column information that you can use to look that up. That's how the demo SSP script works.

Allan