问题描述:

I am trying to reproduce this datatables example here

But if I use the php code below i get the following

and a 500 Internal Server Error.

Which brings me here which points me to look at the server logs here

ls -lt /var/log/ but nothing obvious is standing out at this stage.

However if I use alternative php code below I can get it to work. That is using the same servername, username, password and dbname with both code versions.

Can anyone spot any obvious difference?

I am thinking there might be something wrong with this part of the code but I am not very good at debugging in php:

require( 'ssp.class.php' );

echo json_encode(

SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )

);

This is the JSBIN without the connection to the DB

MORE DETAIL:

This is what my database looks like:

mysql> select * from datatables_demo;

+----+------------+-----------+-------------+--------+------------+--------+

| id | first_name | last_name | position | office | start_date | salary |

+----+------------+-----------+-------------+--------+------------+--------+

| 1 | Tiger | Nixon | Accountant | Tokyo | 2016-11-08 | 320800 |

| 2 | Garrett | Winters | Accountant2 | Tokyo | 2016-11-08 | 170750 |

| 3 | Ashton | Cox | Accountant3 | Tokyo | 2016-11-08 | 86000 |

| 4 | Cedric | Kelly | Accountant4 | Tokyo | 2016-11-08 | 433060 |

+----+------------+-----------+-------------+--------+------------+--------+

4 rows in set (0.00 sec)

PHP code from example, CAN't get this to work:

<?php

/*

* DataTables example server-side processing script.

*

* Please note that this script is intentionally extremely simply to show how

* server-side processing can be implemented, and probably shouldn't be used as

* the basis for a large complex system. It is suitable for simple use cases as

* for learning.

*

* See http://datatables.net/usage/server-side for full details on the server-

* side processing requirements of DataTables.

*

* @license MIT - http://datatables.net/license_mit

*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* Easy set variables

*/

// DB table to use

$table = 'datatables_demo';

// Table's primary key

$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.

// The `db` parameter represents the column name in the database, while the `dt`

// parameter represents the DataTables column identifier. In this case simple

// indexes

$columns = array(

array( 'db' => 'first_name', 'dt' => 0 ),

array( 'db' => 'last_name', 'dt' => 1 ),

array( 'db' => 'position', 'dt' => 2 ),

array( 'db' => 'office', 'dt' => 3 ),

array(

'db' => 'start_date',

'dt' => 4,

'formatter' => function( $d, $row ) {

return date( 'jS M y', strtotime($d));

}

),

array(

'db' => 'salary',

'dt' => 5,

'formatter' => function( $d, $row ) {

return '$'.number_format($d);

}

)

);

// SQL server connection information

$sql_details = array(

'user' => '',

'pass' => '',

'db' => '',

'host' => ''

);

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* If you just want to use the basic configuration for DataTables with PHP

* server-side, there is no need to edit below this line.

*/

require( 'ssp.class.php' );

echo json_encode(

SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )

);

Alternitve PHP code - Can get this to work

<?php

/* Database connection start */

$servername = "localhost";

$username = "root";

$password = "Password1";

$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* Database connection end */

// storing request (ie, get/post) global array to a variable

$requestData= $_REQUEST;

// DB table to use

$table = 'datatables_demo';

$columns = array(

// datatable column index => database column name

0 =>'first_name',

1 => 'last_name',

2 => 'position',

3 => 'office',

4 => 'start_date',

5 => 'salary'

);

// getting total number records without any search

$sql = "SELECT first_name, last_name, position, office, start_date, salary";

$sql.=" FROM $table";

$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees -123");

$totalData = mysqli_num_rows($query);

$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.

if( !empty($requestData['search']['value']) ) {

// if there is a search parameter

$sql = "SELECT first_name, last_name, position, office, start_date, salary";

$sql.=" FROM table";

$sql.=" WHERE first_name LIKE '%".$requestData['search']['value']."%' "; // $requestData['search']['value'] contains search parameter

$sql.=" OR last_name LIKE '%".$requestData['search']['value']."%' ";

$sql.=" OR position LIKE '%".$requestData['search']['value']."%' ";

$sql.=" OR office LIKE '%".$requestData['search']['value']."%' ";

$sql.=" OR start_date LIKE '%".$requestData['search']['value']."%' ";

$sql.=" OR salary LIKE '%".$requestData['search']['value']."%' ";

$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees-456");

$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result without limit in the query

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length.

$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees-789"); // again run query with limit

} else {

$sql = "SELECT first_name, last_name, position, office, start_date, salary";

$sql.=" FROM $table";

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";

$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees 000");

}

$data = array();

while( $row=mysqli_fetch_array($query) ) { // preparing an array

$nestedData=array();

$nestedData[] = $row["first_name"];

$nestedData[] = $row["last_name"];

$nestedData[] = $row["position"];

$nestedData[] = $row["office"];

$nestedData[] = $row["start_date"];

$nestedData[] = $row["salary"];

$data[] = $nestedData;

}

$json_data = array(

"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.

"recordsTotal" => intval( $totalData ), // total number of records

"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData

"data" => $data // total data array

);

echo json_encode($json_data); // send data as json format

?>

from the error warning brings me here

 500 - Internal Error.

The server encountered an error while responding to the request.

Check the server's error log for information as to why this error occurred.

相关阅读:
Top