I have a dynamic table in php/mysql that I show/hide columns by checkbox. The snippet of code below is part of the javascript that hides and recalculates the cells values for a total column.
function toggleVis(button) {
// Toggle column
cells = $$('.t'+button.name);
cells.invoke(button.checked ? 'show' : 'hide');
// Recaulculate total
$$('tr.row').each(function(row) {
// Initialise to zero
var total = 0;
row.down('.total').textContent = total;
// Sum all visible cells
row.select('td').each(function(cell) {
total += cell.visible() ? parseInt(cell.textContent, 10) : 0;
});
// Write the total in the total cell
row.down('.total').textContent = total;
});
}
This works great when the table content is simply numbers, but I now need to create another table with currency values in. This causes the total column to return NaN
presumably due the £ symbol. I format this in php with following code:
<tbody>
<?php do { ?>
<tr>
<td><?php echo $row_rsMISource['Source']; ?></td>
<td><?php echo "£".number_format($row_rsMISource['May'], 2, '.', ','); ?></td>
<td><?php echo "£".number_format($row_rsMISource['Jun'], 2, '.', ','); ?></td>
<td><?php echo "£".number_format($row_rsMISource['Jul'], 2, '.', ','); ?></td>
<td><?php echo "£".number_format($row_rsMISource['Aug'], 2, '.', ','); ?></td>
<td><?php echo "£".number_format($row_rsMISource['Total'], 2, '.', ','); ?></td>
</tr>
<?php } while ($row_rsMISource = mysql_fetch_assoc($rsMISource)); ?>
</tbody>
This outputs values such as £10,169.62, £7,053.00 or £.0.00
Is it possible to have the cells formatted with currency while still using the above posted js?
4 + '£4'; //NaN
4 + parseFloat('£4.3'.replace(/[^\d\.]/g, '')); //8.3
That removes non-numeric characters from the string, and coerces the string to a number (so you get 8.3
, not "44.3"
).
If you live in a country where the comma is used as the decimal separator rather than the period, replace \.
with ,
[EDIT - for your specific example:]
row.find('td').each(function() {
total += $(this).is(':visible') ? parseFloat($(this).text().replace(/[^\d\.]/g, '')) : 0;
});
Quite a few code changes there.