Does this work with PHP and MySQL

beauford

Hi there,

For three days now off and on I have tried to get datatables to work, along with a few other table sorters - and for the life of me I can't figure it out. All the example files I have tried work, but they also have hard coded data. As soon as I try to use a WHILE loop in PHP to get the data from MyQL all of these table sorters do not work, I would assume this is possible. Is there any working examples that show how use PHP and MySQL with datatables as it appears the way I have it is not working.

Thanks

zlen

I suppose you are using the server-side processing from this page : http://www.datatables.net/examples/server_side/simple.html .

You should do :

$results = SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns );

foreach ($results['data'] as &$result) {
$result[] = '/* Your code here */';
}

echo json_encode(
$results;
);

Hope this will help you

beauford

I wasn't using server side processing, didn't even know it existed. After looking at it, it is a lot more work than I wanted to do. I did get things to work with the default settings, but trying to integrate it with my css and other formatting was more than I could figure out. I know nothing about jquery and just enough about css to get me through. Thanks for the info however.

beauford

So I persevered and got this working using the supplied scripts, I just had to change a lot of the CSS to fit my site.

Having said this, I am still having issues with one thing. I suppose this is due to my lack of understanding of jquery and classes.

I want to add an extra column to my table that is not affected by the jquery. I need to have 4 small icons in this last column and have my customer ID available to them. So when I click on them they go to another page carrying the ID with it (<a href="test.php"?ID>icon</a>.

I have tried 50 ways to Sunday to do this and all I get are json errors.

Is there a way to accomplish this.

I tried the code zlen provided and also go json errors.

Thanks

tangerine

Edited example from my own "columnDefs" initialisation:

    {  
        render: function ( data, type, row ) {
        return '<a href="../Grid/Songs/connections/' + row[0] + '">Show me</a>';
        },
    targets: 7
    },

beauford

Where would I place this? I tried in the head section of my php file but it just freezes. Looked in the jquery.datatables.js and the ssp.class.php but not sure what I would do with these.

Thanks

tangerine

You are initialising your DataTable somewhere, something like this:

$(document).ready(function() {
    $('#example').DataTable();
} );

Read the docs on columnDefs or columns, and expand your initialising code accordingly.
Alternatively search the forum, there are plenty of posts on adding urls to columns.

beauford

I have scoured the docs for this and other information and I am still at ground 0. As a newbie I just don't find the manual very helpful and leaves me more confused. There just doesn't seem to be enough info for me to go forward, I am always left with more questions after I read something and then go around in circles trying to figure out what was meant. I am sure this is works great once you know all the nuances and ways thing work but I am still trying to get a handle on the most basic of functions. I'll keep playing around, but for my production model I will have to suffer with straight PHP, HTML, and CSS for now.

Thanks

allan

If you can provide a link to the page I (or someone else) will take a look and be able to help diagnose the problem you are having. At the moment we don't have enough information to be able to help resolve the issue.

Allan

beauford

I don't have a link, but I don't think that would helpmuch . My issue is this. I am using the basic examples of a simple server side database. I retrieve data using PHP from MySQL and it creates a table (NAME ADDRESS CITY PHONE EMAIL). This works fine with and I can sort, paginate, and search.

What I need to do is have one more column after email column with the following.

<a href="view-receipts.php?cid=php variable from get"><img src="images/view.png" width="16" height="16" alt="View Receipts" title="View Receipts"></a>
<a href="add-receipt.php?cid=php variable from get"><img src="images/addreceipt.png" width="16" height="16" alt="Add Receipt" title="Add Receipt"></a>      
<a href="edit-customer.php?cid=php variable from get"><img src="images/edit.png" width="16" height="16" alt="Edit Customer" title="Edit Customer"></a>
<a href="email.php?cid=php variable from get"><img src="images/email.png" width="16" height="16" alt="Email Invoice" title="Email Customer"></a>

Here is my head section of my main page.

<script type="text/javascript" src="include/js/jquery.js"></script> 
<script type="text/javascript" src="include/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function() {
    $('#example').dataTable( {
        columnDefs:[
            {visible:false,targets:0}
        ],  
        "pagingType": "full",
        "lengthMenu": [ 15, 25, 50, 75, 100 ],
        "processing": true,
        "serverSide": true,
        "ajax": "include/server_processing.php"
    } );
} );
</script> 

This is the layout of sever_processing.php.

$columns = array(
    array(
        'db'        => 'date_added',
        'dt'        => 0,
        'formatter' => function( $d, $row ) {
            return date( 'jS M y', strtotime($d));
        }
    ),
    array( 'db' => 'fullname', 'dt' => 1 ),
    array( 'db' => 'address',     'dt' => 2 ),
    array( 'db' => 'city',  'dt' => 3 ),
    array( 'db' => 'pc',  'dt' => 4 ),
    array( 'db' => 'phone',     'dt' => 5 ),
    array( 'db' => 'email',     'dt' => 6 ),
);

Thanks to anyone that can guide me in the right direction or explain this in a "for Dummy's" language.

Cheers

allan

What I need to do is have one more column after email column with the following.

Use columns.data set to null and columns.defaultContent set to match the required HTML.

There is an example of how to do that here - see lines 75-77 in the Javascript shown below the table.

Allan

beauford

<<---- SOLVED ---->>

Thanks for the pointer. I had something similar before but could not get it to work. I still can not get it to work completey but a lot closer as the last column is showing up now, but now I am getting the following error:

DataTables warning: table id=example - Requested unknown parameter 'cid' for row 0. For more information about this error, please see http://datatables.net/tn/4

This is what I have in my php file now. The columns section matches the $columns array in the server_processing.php script.

<script> $(document).ready(function() { $('#example').dataTable( { columnDefs:[ {visible:false,targets:0} ], "pagingType": "full", "lengthMenu": [ 15, 25, 50, 75, 100 ], "processing": true, "serverSide": true, "ajax": "include/server_processing.php", columns: [ { data: "cid" }, { data: "date_added" }, { data: "fullname" }, { data: "address" }, { data: "city" }, { data: "pc" }, { data: "phone" }, { data: "email" }, { data: null, className: "center", defaultContent: '<a href="view-receipts.php?cid=0"><img src="images/view.png" width="16" height="16" alt="View Receipts" title="View Receipts"></a>' } ] } ); } ); </script>

If I take out the cid line I then get the error on date_added, etc. etc.

Cheers

beauford

<<---- SOLVED ---->>

Still trying to figure this however.

I have 7 columns, but my first one is hidden. In the last column, I have 4 links that I want to edit the URL for GET variables. So how would I get the details.php?ID='THE HIDDEN COLUMN INFORMATION"

I tried this with no luck. I have seen several other ways as well but haven't got any of them to work ether. This is actually the most important thing I need from this whole thread.

defaultContent: '<a href="view-receipts.php?cid='+data[0]+'"><img src="images/view.png" width="16" height="16" alt="View Receipts" title="View Receipts"></a>',

columns: [ { data: "0" }, { data: "1" }, { data: "2" }, { data: "3" }, { data: "4" }, { data: "5" }, { data: "6" }, { data: "7" }, { data: null, className: "center", defaultContent: '<a href="view-receipts.php?cid='+data["0"]'"><img src="images/view.png" width="16" height="16" alt="View Receipts" title="View Receipts"></a>', } ]

Any ideas.

Thanks