{"id":3462,"date":"2018-06-12T16:07:06","date_gmt":"2018-06-12T16:07:06","guid":{"rendered":"https:\/\/max-drake.cc\/?p=3462"},"modified":"2018-06-18T11:00:43","modified_gmt":"2018-06-18T11:00:43","slug":"datatables-for-dynamic-database-queries-for-tables-on-web-pages-export-tools","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=3462","title":{"rendered":"DataTables for dynamic database queries for tables on web pages & export tools"},"content":{"rendered":"

I have been interested in displaying tabulated data on a web page from a Database, exploring what is out there to use.<\/p>\n

WordPress has a couple of Free Table add-in’s but they are usually only available for uploading static data to your website (or from Excel\/CSV).<\/p>\n

There is one WordPress Add-in that read from a database but it was quite hard to format. I used it in this example on my property demo website<\/strong><\/a>. The plugin is<\/p>\n

EZ SQL Reports Shortcode Widget and DB Backup<\/a><\/strong>. As you can see from the above the formatting is not very elegant. There was also only a certain number that you could use on one website before they became unmanageable. There are other add-ins but you pay for them.<\/p>\n

I had come across dataTables<\/strong><\/a> before but had not used them. I decided to give them a try.<\/p>\n

My Contabo VPS has been giving me grief in not being able to load these tables properly, for some reason they block the data going through from the PHP part of the code. I found that the table was corrupt. When I tested on another table it worked fine. I have an example on my Property Site with multiple tables in iFrames<\/strong><\/a>. ( The reason they are in iFrames is I haven’t tried to organise the code with WordPress setup at this time)<\/p>\n

In cases like these I use an alternative site, the AwardSpace web hosting service, and have got the tables up and running on that. The link to the table is HERE<\/strong><\/a>, it is still a work in progress.<\/p>\n

\"\"<\/p>\n

Here is an example I am still working on. Mainly exploring the formatting issues currently.<\/p>\n

Things that I want the table to do:<\/p>\n

    \n
  1. Update from database to show relevant information (query to database)<\/li>\n
  2. Condense columns so i can get lots of LEGIBLE columns into the web page<\/li>\n
  3. Basic ordering ( click on column header and it will do asc and dec ordering on a specific column (use search for more refined filtering))<\/li>\n
  4. Have an idea of the data (bottom left number of rows shown & bottom right Pagination)<\/li>\n
  5. Be able to filter the data (using Search box in top right corner)<\/li>\n
  6. After filtering data be able to export it (Copy, CSV, Excel, PDF & Print buttons on Top Left)<\/li>\n<\/ol>\n

    The dataTables calls jquery.js, and a few .css & .js files. I had the experience when working with the Panoramas where I was calling files from another web page via CDN codes (eg\/\/cdn.datatables.net\/1.10.16\/css\/jquery.dataTables.min.css<\/em>) that I had left them live on my webpage and they were changed\/updated and my webpages didn’t work anymore so now I create a sub-directory and put the minimised files in there to be referenced. That ensures that they will continue to operate even if there are updates.<\/p>\n

    I am finding I am doing a bit of fiddling with the filters on the tables.  There ae a lot of styling tools that can be used to be able to style the data on your page, including putting multiple tables on the same page.<\/p>\n

    My initial test was using a :<\/p>\n

    “Select * From housing<\/strong>”<\/p>\n

    which basically went through the whole of the Database Table. This can be very slow, so I am now using more targeted SQL queries to speed up the search, such as:<\/p>\n

    $query=mysqli_query($conn, “SELECT * FROM housing WHERE `Property Code (uk)` like”A-NEWA506%” and `Type Code`= “CEI” ORDER BY `Property Code (uk)`<\/strong>“);<\/p>\n

    So if I want to display lots of information, maybe having low search overhead by making the SQL query more accurate will speed up the table load time.<\/p>\n

    Issues<\/h3>\n

    The issue with the Table  on the Contabo site was a bit of a pain, but after sorting that out (with a bit of hair pulling) I was back on track.<\/p>\n

    My current issue is length of table and what its reading from the database. I uploaded the following CSV for it to read. I have numbered the columns from ‘0’ to ’37’ so I can count which cell in the array that I want that is returned in the call to the database.<\/p>\n

    \"\"<\/p>\n

    \"\"<\/p>\n

    The result below shows:<\/p>\n

      \n
    1. The Sum column in wrong, not column 37 of the array,<\/li>\n
    2. also the Cond columns are not showing at all. I am not sure why this is. It needs a bit more study.<\/li>\n<\/ol>\n

      \"\" I have  run out of patience on this at the moment as I have spent too much time on it. I may visit it later to try and debug that part of it.I have altered column numbers, made the first few 22, 26, 27 and they worked fine, so there may be a limit on reading column headers, but this does not explain why Column 37 is not read, unless there is an upper limit on number of columns being read.<\/p>\n

      Changing the array number  from <th>”.$result[37].”<\/th><\/strong> to <th>”.$result[‘Value’].”<\/th><\/strong> (the column header label works for that item, but doesn’t seem to work for the Cond columns after)<\/p>\n

      There may be a maximum length of columns that it can read but the only  preliminary searching I have found is regarding a previous version that has a limit of 8 columns. As it calls the last column (values) so it must have, in the PHP $result the data from Years 2016 onwards, it just doesn’t want to display anything after column 7\/8.<\/p>\n

      Debugging<\/h3>\n

      Datatables have a debugging process. Go to  http:\/\/debug.datatables.net\/<\/strong> to get the javascript snippet.
      \n\"\"<\/p>\n

      \"\"<\/p>\n

      \"\"<\/p>\n

      \"\"   <\/p>\n

      As it was, this debugging process did not help me regarding column lengths but it is useful tool.<\/p>\n

      Videos and helpful information.<\/h2>\n

      I followed this set of videos for setting up DB link and for buttons:<\/p>\n