Limit Server-Side DataTables Returns To User-Specific Data

MSAF_Inc

I am using DataTables with Server-Side Processing and everything is working great, but my table is returning EVERYTHING from the table! That's a total of 3,147 entries, growing daily...

Example: www.hunterpdx.com/metro_new_copy/view-reports-test.php

Is there a way to limit the return to show data specifically associated with a particular user: WHERE company = $_SESSION['company']?
I'm sure this is a feature provided by DataTables (it HAS to be!), but I have no idea how to set this up.

I'm using the basic initialization code (even left the table ID the same):

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../server_side/scripts/server_processing.php"
} );
} );
[/code]

The only things I've changed on the server_processing.php are the aColumns array and the database connection information:

[code]

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'company', 'bldg', 'report', 'freq', 'report_date', 'file_path' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "report_id";

/* DB table to use */
$sTable = "uploads";

/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "";
$gaSql['db'] = "members";
$gaSql['server'] = "localhost";

[/code]


[code]
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

fbas

Your instincts are correct, you do need to add more filtering to your WHERE condition. The question of where you are storing/retrieving this company name is the open question.

You probably want to modify by pre-populating the WHERE condition with the company portion. The rest of the code will not need to be touched.

[code]
$sWhere = "WHERE company = '" . $_SESSION['company']. "' ";
[/code]

MSAF_Inc

That was it! I had to add a couple more things to make it work right, but that was it!
First: I added [code]session_start();[/code] at the top of server_processing.php
Second: I added a user-specific variable [code]$userCompany = $_SESSSION['company'];[/code]
Third: I plugged this in with the same format you gave, changing [code]$sWhere = " ";[/code] to [code] $sWhere = "WHERE company = '".$userCompany."' ";[/code]
Finally: To make sure the search by word filter didn't go around this, I changed [code]
$sWhere .= ')'; [/code] to [code] $sWhere .= ") AND company = '".$userCompany."'";[/code]

It works! Thanks! Kudos also go out to Maximus2012 at StackOverflow who helped me figure out the missing server_start() call.