How to make Datatables read my own php server side script file?

dataphpmysql

Hello everyone,

I've been trying for like two whole days to solve my issue but nothing, so decided to ask.

So the table my site is using is with server-side processing like the page in this example: https://datatables.net/examples/server_side/simple.html

However, I want to be able to write my own server side script php file and for the table to accept it and be able to use its functions like search, number of entries, pagination, etc.

Well following the instructions on that page, there's a server side script php file where it does its connection to the database and displays it to the table. However it is very basic and I'm not able to to the "Select * FROM ___ WHERE __ etc etc" or any sql command that I want at all. I have very limited control using that format and just want to use mine. So I did my own server side script but apparently it needs to use this:

require( 'ssp.class.php' );

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

or something and is just very confusing since there's way too many errors with json and I don't even want to use json or the ssp.clss.php, I don't need nor want to use none of those two things, I just want to use my own script that doesn't even have that. Is this possible?

So how can I use my server side script and make the table call it correctly and make it display successfully in the table and use all the functions like search, etc?

Thanks in advance.

tangerine

So I did my own server side script but apparently it needs to use this:

require( 'ssp.class.php' );

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

If you use your own server-side script, you would use it instead of ssp.class.php.

Instead of SSP::simple() you would call your own function in your own server-side script. The result must be returned in the required JSON format.

Perhaps you need to spend more time with the documentation.

dataphpmysql

But that wouldn't work because then that would mean that I'll need to use the code before that which is:

<?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' => ''
);

That code is very limited; I can't even write sql commands. So basically, can I just use one php server side script file? Like this (Taken from: https://w3schools.com/php/showphpfile.asp?filename=demo_db_select_oo

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?> 

That's literally all I'm trying to do, write a php file like the one above and make datatables read it in the table without having to call json or the ssp.class or having to use the first line of code.

Is this possible?

Thanks.

tangerine

I don't think I can make it any clearer.

A. You can use any server-side process you like.

B. DataTables requires its data to be submitted as JSON.
An example of the JSON format is under the Ajax tab here:
https://datatables.net/examples/data_sources/ajax

C. See also the parameters required to be returned along with the data:
https://datatables.net/manual/server-side#Returned-data

D. Do spend some time with the documentation. It is very detailed.

dataphpmysql

Thanks for your time for trying to help, however, I honestly don't think you're being clear.

For starters, the example you gave me https://datatables.net/examples/data_sources/ajax is from a static page, not server side.

So using the server side page as example https://datatables.net/examples/server_side/simple.html

It indicates a server side script that I don't want to use since I want to use mine, and the one it uses is:

<?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 )
);

You then tell me that I need to replace the ssp.class.php with my own php page, which I then tell you that wouldn't work cause it would leave the upper code intact, code which I don't intend to use since I want to use mine. Your reply didn't address this issue at all.

I then proceed on giving an example as to what script I wanted to load like this one

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?> 

and how to make that happen. You then give me an ajax and json example that doesn't really explain how to add the above code instead of the script they use.

So I'm still stuck with the same issue.

So let me try again, how can I add the above code WITHOUT using this:

<?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 )
);

Any suggestion would be appreciated. Thanks.

dataphpmysql

Ok, I may have figure it out, however ajax/json weren't needed so not sure if I'm doing it the right way even though it works. Can someone give me their opinion and see if this is a good practice and the correct way of doing it?

So basically what I did was add the php server side process script page on the html table between <thead> and <tfoot>. So for example:

<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>

       <?php include ("serverscript.php"); ?>

        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

And then the "serverscript.php" would be something like this:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>  

That's all I used without having to use ajax/json and It works fine. However is this the correct way to do it? If not, how can I use ajax/json to make it happen?

Thanks in advance.

tangerine

Your code above doesn't actually make any use of DataTables.

If you actually intend using DataTables, you will have to start by looking through the documentation.

user348280

From my experience, DataTables (ssp.class.php) is easier to start off with than using the large-zip-package Editor.Php. Am I correct? (I am rather new too).

But sounds like you are using neither.

allan

Personally I would have said the Editor PHP was easier to use since it has full documentation whereas the ssp class doesn't. But then I'm the author of both, so I'm biased!

Allan

dataphpmysql

So I still haven't really received a response on how to incorporate my php script to datatables.

Can anyone help?

Thanks.

allan

I would suggest that you output the data you have retrieved from the database as JSON (see the PHP documentation for json_encode). Then use DataTables Ajax abilities to load the data in.

Allan