How to use 'Where' in server side processing sample

tjyoung

HI,
I'm using the server side processing php file for a table and its working great. Only catch now is I need to know how I can select only a certain userID instead of it selecting the entire database table. I'm obviously a newbie...

I don't know how to put in the selection criteria. I can get the userID I'm after, just don't know how to put it in the script.

fbas

There will be several ways to do this, and the best option depends on what your data looks like (how unique your IDs are - are substrings of some ID's equal to other ID's? etc).

Probably the most secure way to exactly match an ID is to add a parameter to the fnServerData function, and add a corresponding piece in the server_processing.php script to use it.

[code]
$(document).ready(function() {
oTable = $('#example').dataTable( {
"fnServerData" : function ( sSource, aoData, fnCallback ) {
// push parameter onto the aoData array.
aoData.push( { "name": "id", "value": id } ); // <--- replace id with your id

// send request to server, use default fnCallback to process returned JSON
$.ajax( {
"dataType": 'json',
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
} );
[/code]

and on the server side look for the id value to add to your WHERE clause. (change the id column name as necessary, I've used "id" as the id column name in this code)
[code]

$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

tjyoung

Thanks I'll give it a try. Have to get my head wrapped around it :)

fbas

If you have questions on any part, I can explain more. Let me know if you get stuck.

emelianenko

Hello

I have seen how magnifically you have solved his query. Mine is about the same, so I was trying to reproduce what you indicated for him. Yet, I m not getting any changes in narrowed results as I wanted.

The thing is that I have a series of Tabs (using the JQUERY plugin) .

Before I was using the index page where a code like this would be for example for the Tab "Shops":

[code]


$(document).ready(function() {
$('#shops').dataTable( {
"bProcessing": true,
"bServerSide": true,
"bJQueryUI": true,
"sAjaxSource": "datatables/php/server_processing_shops.php",

} );
} );



[/code]

and then I would be using the typical Server side script, one different table for each different Tab and and server side script so I did not need to include any Where clause because each server side script would be just pulling all of the results from its respective table.

However, as I am changing web and etc now I am going to work with a single Table from which the different server side scripts will be pulling each its part of that big 1 table. In order for that to happen I am including the Where clause like here. For example, I want that in the Tab "Shops" I get only those which are located in Krakow. Yet, I am getting all from everywhere, so it is not making any difference:


[code]


/* Individual column filtering */

for ( $i=0 ; $i

emelianenko

And if I modify the part of the Tab code, then nothing is shown. Somewhat all is messed up.

[code]


$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "datatables/php/server_processing_shops.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "town_shop", "value": "Krakow" } );
$.getJSON( sSource, aoData, function (json) {
/* Do whatever additional processing you want on the callback, then tell DataTables */
fnCallback(json)
} );
}
} );
} );





[/code]

gunjannigam

Hi,
I might not be correct but as you mentioned when you modify the part of the Tab code, then nothing is shown, means there is some problem with server side script. Your javascript client code looks correct.
Have a look at the server script code mentioned by fbas in the first comment http://datatables.net/forums/discussion/6084/x&page=1#Item_2
or server script mentioned by Emeka here
http://www.datatables.net/forums/discussion/2103/date-range-filter-serverprocessing/p1
I tried Emeka script and it works perfectly fine for me

fbas

change "Krakow" to your field name for the town shop. I used "town_shop" in the example below.

[code]/* Individual column filtering */

for ( $i=0 ; $i

tjyoung

Hi fbas... I'm back with yet another related question.
If I need to add an additional 'Where' to my initial question (your solution worked perfectly by the way). How do I add a second 'where'.

In my initial question I was trying to pass the 'id' along to filter the results which worked great. Now I need to add an additionall condition (in my case: campaign_id). So the table will only show a certain campaign ID for a specific user (id).

allan

If I understand correctly, you want to do something like this just after the rest of the $sWhere string is built up (i.e. the end of the block above):

[code]
if ( $sWhere == "" ) {
$sWhere = "WHERE campaign_id = ".mysql_real_escape_string($_GET['campaign_id']);
}
else {
$sWhere .= " AND campaign_id = ".mysql_real_escape_string($_GET['campaign_id']);
}
[/code]

Regards,
Allan

fbas

as Allan points out, just add more " AND _____ = '______' " phrases to the WHERE clause. the AND will require that all values are needed in order to return a row.

in Allan's code above he checks to see if there's an existing $sWhere (value is not an empty string). if it is, write "WHERE _____" else tack on " AND _______" to the existing WHERE phrase.

tjyoung

Fantastic, too simple. I have to say the datatables is brilliant. I'm putting together an app that I wasn't too sure how flexible this would be but it has been absolutely perfect. I haven't found a 'can't do that' yet.

allan

@fbas - doh! Thanks for pointing that out - code corrected!

[quote]tjyoung said: I have to say the datatables is brilliant.[/quote]

Thank you! If you find a "can't do that" let us know and I'll see what can be done ;-)

Allan

tjyoung

Well, I thought it would be simple...
Here is the existing code. I can't figure out how to tack on the extra 'campaign_id' along with the account_id.
[code]

// if account_id is specified, add it to the WHERE clause
if ( isset($_GET['account_id']) && $_GET['account_id'] != "" ) {
if ($sWhere) $sWhere .= " AND account_id=".mysql_real_escape_string( $_GET['account_id'] );
else $sWhere = " WHERE account_id=".mysql_real_escape_string( $_GET['account_id'] );
}
[/code]

Also, how to add that extra variable on to my datatable code. Below is my existing code passing the account_id variable:

[code]


/* POST data to server */
$(document).ready( function () {
var oTable;
oTable = $('#example').dataTable( {
"sPaginationType": "full_numbers",
"bProcessing": false,
"bServerSide": true,
"bJQueryUI": true,
"aaSorting": [[ 6, "desc" ]],
"sAjaxSource": "application/modules/account/scripts/incoming_calls.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some data to send to the source, and send as 'POST' */
aoData.push( {"name": "account_id", "value": "<?php echo $id; ?>"} );
$.ajax( {
"dataType": 'json',
"type": "GET",
"url": sSource,
"cache": false,
"data": aoData,
"success": fnCallback
} );
}
} );
var newtimer = setInterval( function () {
oTable.fnReloadAjax( null, null, true );
}, 5000 );
} );



[/code]

allan

I think that looks very close, but this little bit:

[code]
if ($sWhere)
[/code]

I think needs to be:

[code]
if ($sWhere != "")
[/code]

since there is $sWhere = ""; further up in the code - which would mean that $sWhere is value is actually defined (although does PHP do something weird with it - I suppose it's possible!).

If that doesn't work - I'd suggest echoing out $sWhere and then looking at it in the return.

Allan

tjyoung

hi
that code is what i have currently without the additional variable in. im trying to add a campaign_id as well as the account_id that is in there now. that code works great. So my question was how to add a second variable? in my case the campaign_id along with what I have now.

allan

Oh I see. Can you just do exactly the same thing again:

[code]
if ( isset($_GET['account_id']) && $_GET['account_id'] != "" ) {
if ($sWhere) $sWhere .= " AND account_id=".mysql_real_escape_string( $_GET['account_id'] );
else $sWhere = " WHERE account_id=".mysql_real_escape_string( $_GET['account_id'] );
}

if ( isset($_GET['campaign_id']) && $_GET['campaign_id'] != "" ) {
if ($sWhere) $sWhere .= " AND campaign_id=".mysql_real_escape_string( $_GET['campaign_id'] );
else $sWhere = " WHERE campaign_id=".mysql_real_escape_string( $_GET['campaign_id'] );
}
[/code]

Allan

azhararmar

thank you so much, it helped me solve my problem :)

chiragpatel

i Would like to make a point here that how can i use the range in the where cluse.
EX: how can i get the records which have id > 5 and id< 10.