Fetch specific rows from MySQL with datatables

I’m trying to fetch specific rows in a MySQL table with specific “basket_id”s and display them in the jQuery DataTables plugin.

In the PHP server-side script for the DataTables AJAX request, I specify the $user->basketId() in a WHERE clause for MySQL..

Let me walk you through it..

1. User signs in.
2. USER class stores user session data. (use public methods to retrieve data)
3. In dashboard user clicks ITEMS and sees a datatable.

The problem is that all users are seeing all items from all baskets.

I have verified that the $user->basketId() method return the correct basket ID for the user.

<?php

require_once $_SERVER['DOCUMENT_ROOT'] . "/includes/init.php";

header('Content-Type: application/json; charset=utf-8');

$con = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME);

$output= array();
$sql = "SELECT * FROM items WHERE basket = ".$user->basketId()."";

$totalQuery = mysqli_query($con,$sql);
$total_all_rows = mysqli_num_rows($totalQuery);

$columns = array(
    0 => 'id',
    1 => 'name',
    2 => 'photo',
    3 => 'quality',
    4 => 'locations',
    4 => 'suppliers',
);

if(isset($_GET['search']['value']))
{
    $search_value = $_GET['search']['value'];
    $sql .= " OR name like '%".$search_value."%'";
    $sql .= " OR quality like '%".$search_value."%'";
    $sql .= " OR locations like '%".$search_value."%'";
    $sql .= " OR suppliers like '%".$search_value."%'";
}

if(isset($_GET['order']))
{
    $column_name = $_GET['order'][0]['column'];
    $order = $_GET['order'][0]['dir'];
    $sql .= " ORDER BY ".$columns[$column_name]." ".$order."";
}
else
{
    $sql .= " ORDER BY name desc";
}

if($_GET['length'] != -1)
{
    $start = $_GET['start'];
    $length = $_GET['length'];
    $sql .= " LIMIT  ".$start.", ".$length;
}   

$query = mysqli_query($con,$sql);
$count_rows = mysqli_num_rows($query);
$data = array();
while($row = mysqli_fetch_assoc($query))
{
    $sub_array = array();
    $sub_array[] = $row['name'];
    $sub_array[] = '<img src="'.$config['app_url'].'includes/assets/img/item_photos/'.substr($row['photo'], strrpos($row['photo'], '/') + 1).'" class="img-fluid rounded-circle dt-img" alt="Item Photo">';
    $sub_array[] = $row['quality'];
    $sub_array[] = $row['locations'];
    $sub_array[] = $row['suppliers'];
    $sub_array[] = '<button class="btn btn-outline-info btn-sm update-class-modal" data-toggle="modal" data-target="#update-class-modal" id="'.$row['id'].'" type="button"><span><i class="fad fa-pencil"></i></span></button> <button class="btn btn-outline-danger btn-sm" data-toggle="modal" data-target="#confirm-delete-class" id="'.$row['id'].'" type="button"><span><i class="fad fa-trash"></i></span></button>';
    $data[] = $sub_array;
}

$output = array(
    'draw'=> intval($_GET['draw']),
    'recordsTotal' =>$count_rows ,
    'recordsFiltered'=>   $total_all_rows,
    'data'=>$data,
);

echo json_encode($output, JSON_PRETTY_PRINT);

I’m not sure why, but it doesn’t matter which user I am logged into and which basket ID is returned, datatables always fetches all the items in the items table.

Answer

So, after doing some researching using all the keywords I could think of I came up with an answer.

DataTables already provides an SSP.php library which is a Server-side Processing Class for PHP.

Using the SSP::complex() method we can define two extra conditions:

SSP::complex( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )

1. $whereResult=null

This is a WHERE clause for data AFTER it has been fetched from the DB table, so this will filter through results after the SQL results are returned.

2. $whereAll=null

This is a WHERE clause for data BEFORE it has been fetched from the DB, so this will add a WHERE condition before the SQL query and will only return results that match.

After everything my final get_items.php AJAX file looks like

<?php

require_once $_SERVER['DOCUMENT_ROOT'] . "/includes/init.php";
header('Content-Type: application/json; charset=utf-8');

// Database connection info 
$dbDetails = array( 
    'host' => 'redacted', 
    'user' => 'redacted', 
    'pass' => 'redacted', 
    'db'   => 'redacted' 
); 
 
// DB table to use 
$table = 'items'; 
 
// 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.  
// The `dt` parameter represents the DataTables column identifier. 
$columns = array( 
    array( 'db' => 'id',  'dt' => 0 ), 
    array( 'db' => 'name',      'dt' => 1 ), 
    array( 'db' => 'photo',     'dt' => 2 ), 
    array( 'db' => 'quality',    'dt' => 3 ), 
    array( 'db' => 'locations',    'dt' => 4 ),
    array( 'db' => 'suppliers', 'dt' => 5 )
);
 
// Output data as json format 
echo json_encode( 
    SSP::complex( $_GET, $dbDetails, $table, $primaryKey, $columns, null, "school = {$user->basketId()}" )
);