Adjusting view.php of Tables block to ignore last column

Permalink 1 user found helpful
Hi all,

I am using the tables add-in for the website I am using. I have built a table for charitable donations, and I am using a custom PHP block to total the donation values.

The problem is that the society would like the values as they presently are, with a comma separator (i.e. 1,973) for thousands. However, this then means that the SQL query can't sum the fields as it appears not to like the comma.

I then hit upon the idea of adding an extra column to the table which would have the donation as a pure integer - this could then be summed by the query. But... there appears to be no external way of hiding a table column from display.

Could anyone advise as to how to adjust either the query or the view.php to obtain a total?

Iain

The SQL Query:-
$con = mysql_connect("localhost","web229-swanbank","l180guy");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("web229-swanbank", $con);
$result = mysql_query("SELECT Sum(fieldValue) 
FROM swpTablesRows INNER JOIN swpTablesValues ON swpTablesRows.rowID = swpTablesValues.rowID
WHERE (((swpTablesRows.tableID)=2) AND ((swpTablesValues.fieldID)=6)); ");
echo mysql_result($result,0);


The view.php:-
<?php    defined('C5_EXECUTE') or die(_("Access Denied.")); ?>
<?php   if (!empty($table)) { ?>
<div class="swp-table">
<table class="swp-table-table">
<?php  
// table header
$columns = $table["columns"];
if (!empty($columns)) {
   echo '<tr class="swp-table-head">';
   foreach($columns as $column) {
      if ($column["colspan"] > 1) {
         echo '<th colspan="'.$column["colspan"].'">';
      } else {
         echo '<th>';
      }

 
icowden replied on at Permalink Reply
No-one at all?

Iain
boomgraphics replied on at Permalink Reply
boomgraphics
hello, I looked on google and found that PHP has a function for formatting a number. So you could simply return the query as an integer, and format it with PHP to include the thousands comma.
icowden replied on at Permalink Reply
umm...

Not understanding.

The problem with my query is that it is trying to sum a number which has a comma separator. Where Access seems to ignore the comma, mysql doesn't.

I think I therefore either need a way to convert the fields from strings "1,999" into integers "1999"(not sure how to do this) or a way to convert the view.php so it returns 3 columns of a 4 column table, so that I can have a hideen column which my query can add up properly.

Iain
jordanlev replied on at Permalink Reply
jordanlev
What is the "tables addon"? If it's something you paid for, you should definitely post this in the addon's support forum (click "Support" in the sidebar of that addon's marketplace page). Even if it's a free addon you should still get support (although it might not be timely, depending on the developer).

Since I'm not familiar with this addon I can't say what exactly you need to do, but in general you can convert a number with commas to a straight-up integer by using the intval() function.
icowden replied on at Permalink Reply
Thanks - Tables is a free add on, hence I thought posting here might be more constructive.

I'll have a play with intval though and see if I can get it to work.

Thanks for the tip!

Iain
tallacman replied on at Permalink Reply
tallacman
I think your referring to the CSV Displayer and not the Tables addon. They are both from smart web projects.
icowden replied on at Permalink Best Answer Reply
Hi - I was definitely referring to the Tables add on - which of course isn't free.

I figured out the problem though. Using REPLACE in the SQl query to eliminate the comma enabled me to correctly sum the column. I was then able to output the result using $format so that it had the seperator in it.

Works nicely now!
icowden replied on at Permalink Reply
I think the proper way to do this would be to introduce a new field type for "Money" which would store as integer but incorporate the money formatting.

I may play with this idea when I have some spare time.

Iain