PHP / MySQL / jQuery / bootstrap question

DexDeadly

Hello,

Currently I am using DataTables and its great. However I am trying to do something and I'm not sure exactly how to do it or if its possible. On my PHP page I currently build the table like so:

 <table id="monthlySales" class="table table-bordered">
    <thead>
         <tr>
         <th>Year</th>
         <th>Month</th>
         <th>Reported Gross</th>
         <th>Royalty %</th>
         <th>Royalty Owed</th>
         <th>Advertising Owed</th>
         <th>Technology Owed</th>
         <th>Support Owed</th>
         <th>Total Owed</th>
         <th>Paid</th>
         </tr>
  </thead>
<?PHP
foreach($monthlySales as $ms){
 echo "<tr>";
 echo "<td>" . $ms['reportingYear'] . "</td>";
 echo "<td>" . $ms['reportingMonth'] . "</td>";
 echo "<td>" . money_format("%.2n", $ms['reportedGross']) . "</td>";
 echo "<td>" . $ms['royaltyPerc'] . "</td>";
 echo "<td>" . money_format("%.2n", $ms['royaltyOwed']) . "</td>";
 echo "<td>" . money_format("%.2n", $ms['advertisingOwed']) . "</td>";
 echo "<td>" . money_format("%.2n", $ms['technologyOwed']) . "</td>";
 echo "<td>" . money_format("%.2n", $ms['supportOwed']) . "</td>";
 echo "<td>" . money_format("%.2n", $ms['totalOwed']) . "</td>";
 echo "<td>";
 if($ms['paid']=='0'){ echo "Processing";}
 elseif($ms['paid']=='1'){ echo "<span style='color: green'>Paid</span>";}
 elseif($ms['paid']=='2') {echo "<span style='color: red'>Failed Payment</span>";}
 echo "</td>";
 echo "</tr>";
}
?>
</table>

I think initialize it at the bottom of my page like so.

<script type="text/javascript">
    $(document).ready(function() {
        $('#monthlySales').dataTable({
            'order': [[ 0, 'asc' ]],
            'columnDefs': [
                { orderable: false, targets: [0] }
            ]
        });

    });
</script>

I have a modal that I hope and then using jquery I send the data from my modal form to a php file that adds the info to my database. What I would like to do is upon successful addition to the database I would like to refresh just the table and not have to refresh the entire page. My guess is I'm not building the datatable correctly in order to accomplish this. Can someone help point me to the best way to accomplish this so I don't have to have the entire page refreshed.

Thanks!

dclar43

You'll want to look into the Data Source > Sever Side example.

DexDeadly

Thanks, this should help me out, I already have a connection setup using PDO so I"m going to see if I can integrate the current connection I already have within my connection file. Again appreciate the guidance.

DexDeadly

@dclar43 - So I'm looking at this and I have a question I can't seem to put together. this table shows for different clients in the database. So the one thing I need to do when querying this table for data is to filter it based off a client ID column. This is different for each person. How can I achieve this behavior?

dclar43

You'll have to elaborate some more, I'm not sure what you mean by client id. Could be the logged in user, could be a larger entity like a company, could mean the session, etc.

DexDeadly

@dclar43 - Apologies. In my admin backend I'm building for a franchise software I have a table on the page that will pull sales records. I think I was able to do this by doing the following

<script type="text/javascript">
    $(document).ready(function() {
        $('#monthlySales').DataTable({
            'order': [[ 0, 'asc' ]],
            'columnDefs': [
                { orderable: false, targets: [0] }
            ],
            'processing': true,
            'serverSide': true,
            "ajax": "salesTable.php?id=<?php echo $client['id']; ?>"
        });

    });
</script>

However now i get a MySQL error stating.

DataTables warning: table id=monthlySales - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 5

if I go directly to my page I do get the data returned.

{"draw":0,"recordsTotal":7,"recordsFiltered":7,"data":[["2018","March","$32,135.20","7.00","$2,249.46","$321.35","$642.70","$964.06","$4,177.58","0"],["2017","January","$1,000.00","7.00","$70.00","$250.00","$150.00","$100.00","$570.00","0"],["2017","January","$2,136.00","7.00","$149.52","$250.00","$150.00","$100.00","$649.52","0"],["2017","January","$2,250.00","7.00","$157.50","$250.00","$150.00","$100.00","$657.50","0"],["2017","January","$1,554.00","7.00","$108.78","$250.00","$150.00","$100.00","$608.78","0"],["2017","January","$3,452.00","7.00","$241.64","$250.00","$150.00","$100.00","$741.64","0"],["2017","January","$2,500.00","7.00","$175.00","$250.00","$150.00","$100.00","$675.00","0"]]}

DexDeadly

Actually it looks like I needed to remove the columnDefs and it worked. Now to just style it so it is 100% width.

allan

Make the HTML:

<table id="monthlySales" class="table table-bordered" style="width:100%">

Allan

DexDeadly

@allan thank you that is what I ended up doing. Simple. I guess here is the only other questions, is it possible to order by the table key in descending order? I'd like to do year then month but dont know if that is possible but the key is always greater for the most recent so If I can use that it would be fine. I'm not displaying this id field as a column. Thanks.

dclar43

Is order() or order what you're looking for?

As an initialization option:

order: [[0, 'desc'], [1, 'desc']]

This would set the default ordering to be column 0(year) greatest to least, then by column 1(month) greatest to least. It looks like you have ordering disabled so this initial ordering should stick.

This is provided you're loading all of the table data at once from the server. If you do something like loading one page's worth at a time then you have to do some more work on the server-side script to account for that.

DexDeadly

@dclar43 Will the month column do it alphabetically though, if so I'm looking to do it by actual month order.

kthorngren

I'm not familiar with any sorting plugins that will sort by the month's name but you can look. If there are none you could create a custom sorting plugin.

Another alternative is to render the month's value into the column just for sorting. You can use something like this:

columnDefs: [
  { targets: 1,
   render: function (data, type, full, meta) {
            if (type == "sort" || type == 'type') {
               return <code return 1 if January to 1, 2 if February, etc>
            }
       return data;
   }
  }
]

Kevin

allan

The Moment plug-in introduced in this blog post is the best available for flexibility when working with dates and times.

It will probably be included in the next major version of DataTables in fact (although with the Moment dependency entirely optional should you not need it).

Allan