How to use 'Where' in server side processing sample
How to use 'Where' in server side processing sample
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.
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.
This discussion has been closed.
Replies
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
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
[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]
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
[code]/* Individual column filtering */
for ( $i=0 ; $i
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).
[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
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.
[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
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]
[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
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.
[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
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.