2 datatables, order/order details

crush123

I have a page where i want to show orders/order details.

At the moment, Im not sure if i want to try a single table, trying to incorporate parent/child info, (would be cool, but I think its a bit beyond my abilities - or 2 tables the top one showing orders, the bottom one order details.

My objective is to update the content of the bottom table based on the selected row of the top one.

(the 2 tables have a json source based on relational tables, linked by a one to many relationship on orderid.)

so far, I have populated both tables, and using

$('#orders tbody').on( 'click', 'tr', function () {
var myobject = tblorders.row( this ).data();
var rowid = myobject.DT_RowId;
var myid = rowid.substr(4);
//alert(mystring);
alert(myid);
    } );

I can retrieve the row object as a string and then, with a bit of manipulation, get my id as an integer

is there a better way to manipulate the row object to get the rowid ?

Once this is done, I imagine I would use this value as an ajax variable and reload the orderdetails using a callback ? but i can't get the syntax.

If i debug, i can see that the json is created correctly

$('#orders tbody').on( 'click', 'tr', function () {
var myobject = tblorders.row( this ).data();
//var mystring = (JSON.stringify(arrofobject));
var rowid = myobject.DT_RowId;
var myid = rowid.substr(4);
alert(myid);
$.ajax ({
            url: '/plugins/shop/ajax/ajax_neworderdetails.php',
            data: {DetailOrderID: myid},
            dataType: 'json',
            success: function (json) {
            tblorders.ajax.reload( null, false );
            }
        })
    } );

http://test2.forthwebsolutions.com/plugins/shop/orders.php

allan

Once this is done, I imagine I would use this value as an ajax variable and reload the orderdetails using a callback ?

Yes - you would use ajax.url() to get the url with the id as part of the URL (a GET parameter for example) and then simply use the ajax.url().load() method to load the new data.

For getting the id, since you are using DT_RowId it is assigned to the tr element as its id so you could simply use: var id = this.id.substr(4);.

Allan

crush123

Nearly there.

I am using editor on many of my pages, and my json data provided for the order details is formed by creating an editor instance

$editor = Editor::inst( $db, 'tblname', 'ItemID' );//table name and PKey(defaults to ID)

and the resulting json looks like this...

{"data":[{"DT_RowId":"row_1","tblorderdetails":{"DetailOrderID":"1","DetailProductID":"74","DetailName":"a","DetailPrice":"4","DetailSKU":"Jumper - Senior - - 7-8 years","DetailQuantity":"1"}},{"DT_RowId":"row_2"....

When i create a json source without an editor instance,

//pass the id of the chosen row to filter the order details available
$orderfilter = isset($_GET['DetailOrderID'])?$_GET['DetailOrderID']:"1";
$data['orderdetails'] = $db
    ->sql( "SELECT DetailID, DetailOrderID, DetailProductID, DetailName, DetailPrice, DetailSKU FROM tblorderdetails WHERE DetailOrderID = ".$orderfilter."")
    ->fetchAll();

echo json_encode($data);

the array is formed ok, but missing {"data":[{"DT_RowId":"row_1"... at the start

How do i format the json to replicate that formed using the editor

allan

Simply wrap it in an array with a data property. For the DT_RowId add that to your select query (myId as DT_RowId). If you need to add the row_ part you might need to loop over the data, or use an SQL function to do it.

Allan

crush123

sorry if i need my hand holding here.

this is the json returned from the original editor instance

{"data":[{"DT_RowId":"row_1","tblorderdetails":{"DetailOrderID":"1","DetailProductID":"74","DetailName":"a","DetailPrice":"4","DetailSKU":"Jumper - Senior - - 7-8 years","DetailQuantity":"1"}},{"DT_RowId":"row_2","tblorderdetails":{"DetailOrderID":"2","DetailProductID":"76","DetailName":"Jumper 1","DetailPrice":"5","DetailSKU":"Jumper - Nursery - - 3-4 years","DetailQuantity":"1"}},{"DT_RowId":"row_3","tblorderdetails":{"DetailOrderID":"3","DetailProductID":"58","DetailName":"Nursery Jumper","DetailPrice":"3","DetailSKU":"Jumper - Nursery - - 3-4 years","DetailQuantity":"1"}},{"DT_RowId":"row_4","tblorderdetails":{"DetailOrderID":"3","DetailProductID":"73","DetailName":"Pre Prep Blouse","DetailPrice":"2.5","DetailSKU":"Girls Blouse - Short Sleeved - Pre-Prep - - 11\"","DetailQuantity":"1"}}],"options":[]}

using sql and the datatables library, this is my sql to get the a subset of the above, using my row id as a filter parameter

    $data['tblorderdetails'] = $db
    ->sql( "SELECT concat('row_',DetailID) AS DT_RowId, DetailOrderID, DetailProductID, DetailName, DetailPrice, DetailSKU FROM tblorderdetails WHERE DetailOrderID = ".$orderfilter."")
    ->fetchAll();

echo '{"data":[{"DT_RowId":"row_3",'. json_encode($data)."]}";

i have recreated the DT_RowId by concatenating a string to the front of my id, and played with the data before json encoding it, and i cant get the same json structure,.

Am i going about it the right way ?

allan

I would suggest:

echo json_encode( [
  "data" => $data['tblorderdetails']
] );

(if you are using PHP 5.4 or newer - use array() rather than square brackets otherwise).

edit - I just spotted that you are reading the query result into $data['tblorderdetails']. I'm not sure what $data is therefore, but I've updated my code above.

Allan

crush123

Okay.

I couldn't get the json to format in the same way as it does when using editor, so I took a step back.

As I don't need to edit my table in this instaqnce, I just populated my table using a mysql recordset, as a json source, so once this was working ok, i introduced a parameter.

All is good.

The page 'works', in so far as the order details are reloaded according to the order row clicked.

Not sure if the js is as it should be though.

Here is my function, a lot of it is commented out, as it was calling the ajax json twice

$('#orders tbody').on( 'click', 'tr', function () {
var id = this.id.substr(4);

//alert('id '+id);
//$.ajax ({
            //url: '/plugins/shop/ajax/ajax_orderdetails.php',
            //data: {DetailOrderID: id},
            //dataType: 'json',
            //success: function (json) {
            tblorderdetails.ajax.url('/plugins/shop/ajax/ajax_orderdetails.php?DetailOrderID='+id).load();
            }
        //})
     );

When i look at the network tab in debug mode, i see the response is ok, but the page called has a long number after it, eg

ajax_orderdetails.php?DetailOrderID=2&_=1423133576247

http://test2.forthwebsolutions.com/plugins/shop/orders.php