Is there a workaround for the aLengthMenu option for displaying all compounds? I originally had it as
"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
But when the user selects "Show All" it only shows the default selection
That's an SQL question for an SQL forum. You'll get better answers to SQL questions in an SQL forum, rather than this which is specifically for the client-side DataTables library.
Hi guys, I wonder if the sColumns showing in the JSON cannot be considered as a security issue once we are giving this information "for free" which is exactly the MySQL field names.
IMHO if we make things as difficult as we can to avoid data hijacking or other kinds or threat, the better. Is that something we have to concern about?
I have created a small tutorial for how to use ignited datatables, hope it helps you
http://www.ahmed-samy.com/codeigniter-full-featrued-jquery-datatables/
Hi
Could someone explain to me the workings of the following piece of code:
->add('edit', 'Edit', 'customer_id, first_name, last_name')
what do the $1 $2 $3 in the string stand for.
I'm trying to use this line for a link to go off and edit a record on another page which would be filtered by lets say the customer_id, but I am not having much joy.
I've tried changing the href to
Got it to work with the following method
->add('edit', 'pay', 'purchase_invoice_id');
shows http://192.168.1.81/cloudone/pay_invoice.php?inv=3 in the address bar.
so alls good, thank you for your help, are you able to point me in the direction of an example on how to format the following two items, returned date like 2013-04-14 to display like 14-04-2013 04 or 14/04/2013 in keeping with the UK and 23.00 to £23.00.
Thank you
Hi
I tried your advice, however, I could not get a named pair on the receiving page, I set it up in the following manner for the finnish.
[code]->add('edit', 'pay', 'purchase_invoice_id');[/code]
this gives the following in the receiving address bar
[code]http://192.168.1.81/cloudone/pay_invoice.php?inv=2.[/code]
Whilst I am here, can you point me in the right direction to format the columns for Uk currency and Date.
Ok, I am trying out this plugin. I have data in json format coming back from the database. Table renders correctly in the webpage, but I can not seem to get the data to render in the table. I am using the latest CI, jQuery, the latest datatables.jsa and the CI datatables library. Using a localhost of XAMPP.
[code]
$(document).ready(function () {
var oTable = $('#big_table').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": 'index.php/home/datatable',
"bJQueryUI": true,
"sPaginationType": 'full_numbers',
"iDisplayStart": 20,
"oLanguage": {
"sProcessing": ""
},
"fnInitComplete": function () {
//oTable.fnAdjustColumnSizing();
},
"fnServerData": function (sSource, aoData, fnCallback) {
$.ajax({
'dataType': 'json',
'type': 'get',
'url': sSource,
'data': aoData,
'success': fnCallback
});
console.log(fnCallback);
}
});
});
[/code]
here is the controller
[code]
class Home extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->library('Datatables');
$this->load->library('table');
//$this->load->database();
}
public function index() {
//$this->load->library('table');
$tmpl = array ( 'table_open' => '' );
$this->table->set_template($tmpl);
$this->table->set_heading('First Name','Last Name','Email');
$this->load->view('default'); //subscriber_view
}
//function to handle callbacks
function datatable() {
$this->datatables->select('first,last,email')
->unset_column('id')
->from('subscriber');
echo $this->datatables->generate();
}
}
[/code]
I found a simple tutorial/example online here: http://www.ahmed-samy.com/php-codeigniter-full-featrued-jquery-datatables-part-1/
I have a feeling that my problem lies with the success method inside the ajax call. i know it has to be some newbie mistake that i am making. just cannot see it.
->edit('some_column' , '$1', 'time()') // replace current timestamp.
// ->edit('some_column' , '$1', "strtotime('some_column')") // or convert some_column value to timestamp
->edit('some_column' , '$1', "date('l jS \of F Y h:i:s A', some_column)") // format and replace.
Yusuf,
Thanks for the reply. I understand now about that unset_column method. i commented it out.
As for the $.ajax type?? Every time I change it to a POST, I get a Error Code 500 from the XAMPP. It points back to my home/datatable ci function and it says that the action I have requested is not allowed. Why would this happen with POST and not GET??
which is fine for a one-to-one relationship. However, if I have a one-to-many relationship, how can I get just the oldest many_value from the many table? I'd typically use a sub query but I don't understand how to integrate that into the above select.
Thanks for the response -- unfortunately, I may not have been as descriptive as I should have been as your solution mimics what I've got (which is fine for one-to-one) and gives me a record for each [Service Note] instead of just the most recent. Here's a more concrete example:
[ Service Ticket ]
ST_ID
ST_Request
[ Service Note ]
SN_ID
SN_Value
SN_Date
SN_ST_ID
A given [Service Ticket] can have multiple [Service Note] records. What I would typically use for my SQL would be:
[code]
SELECT ST_Request,
(SELECT TOP 1 SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
) as [Recent Note]
FROM [Service Ticket]
[/code]
How can I emulate this using the toolset? I was looking at sub-queries but those are in the from clause and I didn't follow the examples.
->select('ST_Request, SN_Value')
->from('(SELECT ST_Request,
(SELECT TOP 1 SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
) as [Recent Note]
FROM [Service Ticket]) as datatable_table');
I'm not sure but this may work.. Let me know if it works.
Wow, okay -- it does work with a minor change (LIMIT instead of TOP) although it seems to defeat some of the value of using your toolset. I think I may just put the latest [Service Note].ID in the [Service Ticket] and grab it that way to simplify the code/query.
Be well!
[code]
->select('ST_Request, SN_Value')
->from('
(SELECT ST_Request,
(SELECT SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
LIMIT 1
) as SN_Value
FROM [Service Ticket]) as datatable_table');
[/code]
The edit_column is not working as it should be. It outputs whatever I put as the 3rd param.
Also. If in the SQL I am using a function i.e: group_concat(field) the where and like is applied to that function too when doing a searc, causing an error in the query.
I am using version 1.9
Replies
Is there a workaround for the aLengthMenu option for displaying all compounds? I originally had it as
"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
But when the user selects "Show All" it only shows the default selection
Thanks
--Nick
Have support for SUM with datatables in ajax ?
Thanks in advanced.
Allan
IMHO if we make things as difficult as we can to avoid data hijacking or other kinds or threat, the better. Is that something we have to concern about?
->edit_column('post_date','$1','callback_formatDate(post_date)')
not runing with datatable library. What is the fault?
Have you planed to add the support for postgreSql ?
Otherwise, do you have a workaround to give, so I could start the work since I'm using pgsql with codeigniter, I will need this !
Thanks a lot
http://www.ahmed-samy.com/codeigniter-full-featrued-jquery-datatables/
Could someone explain to me the workings of the following piece of code:
->add('edit', 'Edit', 'customer_id, first_name, last_name')
what do the $1 $2 $3 in the string stand for.
I'm trying to use this line for a link to go off and edit a record on another page which would be filtered by lets say the customer_id, but I am not having much joy.
I've tried changing the href to
->add('edit', 'Edit', 'customer_id, first_name, last_name')
the format is ;
->add($columns, $newstring, $replaces)
$1 refers to customer_id, $2 first_name and $3 is last_name.
->add('edit', 'Edit', 'customer_id')
this should work. customer_id must be selected with $datatables->select(). otherwise it can't replace it.
David
David
->add('edit', 'pay', 'purchase_invoice_id');
shows http://192.168.1.81/cloudone/pay_invoice.php?inv=3 in the address bar.
so alls good, thank you for your help, are you able to point me in the direction of an example on how to format the following two items, returned date like 2013-04-14 to display like 14-04-2013 04 or 14/04/2013 in keeping with the UK and 23.00 to £23.00.
Thank you
David
I tried your advice, however, I could not get a named pair on the receiving page, I set it up in the following manner for the finnish.
[code]->add('edit', 'pay', 'purchase_invoice_id');[/code]
this gives the following in the receiving address bar
[code]http://192.168.1.81/cloudone/pay_invoice.php?inv=2.[/code]
Whilst I am here, can you point me in the right direction to format the columns for Uk currency and Date.
Any assistance or pointers appreciated.
David
[code]
$(document).ready(function () {
var oTable = $('#big_table').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": 'index.php/home/datatable',
"bJQueryUI": true,
"sPaginationType": 'full_numbers',
"iDisplayStart": 20,
"oLanguage": {
"sProcessing": ""
},
"fnInitComplete": function () {
//oTable.fnAdjustColumnSizing();
},
"fnServerData": function (sSource, aoData, fnCallback) {
$.ajax({
'dataType': 'json',
'type': 'get',
'url': sSource,
'data': aoData,
'success': fnCallback
});
console.log(fnCallback);
}
});
});
[/code]
here is the controller
[code]
class Home extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->library('Datatables');
$this->load->library('table');
//$this->load->database();
}
public function index() {
//$this->load->library('table');
$tmpl = array ( 'table_open' => '' );
$this->table->set_template($tmpl);
$this->table->set_heading('First Name','Last Name','Email');
$this->load->view('default'); //subscriber_view
}
//function to handle callbacks
function datatable() {
$this->datatables->select('first,last,email')
->unset_column('id')
->from('subscriber');
echo $this->datatables->generate();
}
}
[/code]
I found a simple tutorial/example online here: http://www.ahmed-samy.com/php-codeigniter-full-featrued-jquery-datatables-part-1/
I have a feeling that my problem lies with the success method inside the ajax call. i know it has to be some newbie mistake that i am making. just cannot see it.
tony
->edit('someprice', '£$1', 'someprice') /// simple replace
->edit('some_column' , '$1', 'time()') // replace current timestamp.
// ->edit('some_column' , '$1', "strtotime('some_column')") // or convert some_column value to timestamp
->edit('some_column' , '$1', "date('l jS \of F Y h:i:s A', some_column)") // format and replace.
@energetic_pixels
$this->datatables->select('first,last,email')
->unset_column('id')
->from('subscriber');
echo $this->datatables->generate();
here is the problem.. "->unset_column('id')" you should first select the 'id' column, then u are able to unset it.
$this->datatables->select('first,last,email')
->from('subscriber');
and another problem is ;
$.ajax({
'dataType': 'json',
'type': 'get',
'url': sSource,
'data': aoData,
'success': fnCallback
});
type should be 'post'
Regards,
Yusuf
Thanks for the reply. I understand now about that unset_column method. i commented it out.
As for the $.ajax type?? Every time I change it to a POST, I get a Error Code 500 from the XAMPP. It points back to my home/datatable ci function and it says that the action I have requested is not allowed. Why would this happen with POST and not GET??
Tony
I will give it a try and report back,your willingness to help the less experienced is much appreciated.
Dave
https://github.com/n1crack/IgnitedDatatables-native-php-version throws a 404.
Currency and date tips worked a treat.
Dave
Given this structure (simplified):
[ one ]
id
one_value
dt_stamp
[ many ]
id
one_id
many_value
dt_stamp
I can use:
->select('id, one_value')
->from('one')
->join('many','many.one_id = one.id','left')
->select('many_value')
which is fine for a one-to-one relationship. However, if I have a one-to-many relationship, how can I get just the oldest many_value from the many table? I'd typically use a sub query but I don't understand how to integrate that into the above select.
Thanks for your time :)
one.one_value as value,
sec_value,
many_value,
another.one_value as real_value ')
->from('one')
->join('many','many.one_id = one.id','left')
->join('another','another.id = one.id','left')
[ Service Ticket ]
ST_ID
ST_Request
[ Service Note ]
SN_ID
SN_Value
SN_Date
SN_ST_ID
A given [Service Ticket] can have multiple [Service Note] records. What I would typically use for my SQL would be:
[code]
SELECT ST_Request,
(SELECT TOP 1 SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
) as [Recent Note]
FROM [Service Ticket]
[/code]
How can I emulate this using the toolset? I was looking at sub-queries but those are in the from clause and I didn't follow the examples.
Thank you!
->select('ST_Request, SN_Value')
->from('(SELECT ST_Request,
(SELECT TOP 1 SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
) as [Recent Note]
FROM [Service Ticket]) as datatable_table');
I'm not sure but this may work.. Let me know if it works.
Regards,
Yusuf
Be well!
[code]
->select('ST_Request, SN_Value')
->from('
(SELECT ST_Request,
(SELECT SN_Value
FROM [Service Note]
WHERE [Service Note].SN_ST_ID = [Service Ticket].ST_ID
ORDER BY SN_Date DESC
LIMIT 1
) as SN_Value
FROM [Service Ticket]) as datatable_table');
[/code]
Also. If in the SQL I am using a function i.e: group_concat(field) the where and like is applied to that function too when doing a searc, causing an error in the query.
I am using version 1.9
This code is newly added I think (3 months ago).
If I delete it everything seems to work, but I'm not sure.