No. of rows returned and sum of row field

11 posts by 3 authors in: Forums > CMS Builder
Last Post: February 15, 2022   (RSS)

By dmn - February 9, 2022

Have two record fields, cat_1_credits and cat_2_credits,  that I am totalling separately and have the totals appear in a column heading over the specific record column, am using this

<?php
$sum = 0;
foreach ($basketRecords as $record) {
$sum = $sum + $record['price'];
}
?>
The total price is: <?php echo $sum ?>

that I got from a 2014 thread https://www.interactivetools.com/forum/forum-posts.php?postNum=2233851#post2233851

I just replace basketRecords with my table name and price with  (record) name - it works great and provides the totals but if one of the records (cat_1_credits or cat_2_credits) has an empty entry I still get the correct total but this as well An unexpected error occurred:

How do I fix this?

By daniel - February 11, 2022

Hi dmn,

Could you copy the code you're currently using that is producing the error? 

Thanks!

Daniel
Technical Lead
interactivetools.com

Hi,

Don't know if it will work but you can try a @ before the $ in the variable that comes up in the error report. That might suppress the error.

Or have a look at https://www.interactivetools.com/forum/forum-posts.php?postNum=2245823 which is about suppressing errors

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By dmn - February 11, 2022 - edited: February 11, 2022

Here it is Daniel, it's a search by date attended (min and max), for past records of 2 fields, cat_1_credits and cat_2_credits, it returns all the fields fine, with correct rows and columns, and you will the see sum value in the header of the tables, it does total them correctly but if one of the fields of either cat-1-credits or cat_2_credits, is empty there  is an error appears(eg.(An unexpected error occurred: #8691)) before the total being created by the empty field, the sum equation doesn't account for null values. I've searched the forum and online to try and account for null or empty fields but can't get anything to work.

<form method="GET" action="<?php echo $_SERVER['PHP_SELF'] ?>" name="searchForm" autocomplete="off">


<input name="search" value="1" type="hidden">

<!-- bugfix: hitting enter in textfield submits first submit button on form -->
<input type="submit" style="width: 0px; height: 0px; position: absolute; border: none; padding: 0px">

<!-- main panel content -->

<!-- search -->
<div class="form-horizontal">

<!-- simple search -->



<div class="hideShowSecondarySearchFields" data-animate="slide">
<div class="row" style="margin-bottom: 5px;">
<div class="span3">
<label class="control-label">
Date Attended Min <br>drop off year start </label>
</div>
<div class="span6">
<div class="col-xs-12 col-sm-6 col-md-4 col-lg-3" style="padding: 0 6px 0 0">
<input class='text-input form-control' type='text' name='date_attended_min' value=''> </div>
<div class="help-block col-xs-12 col-sm-6 col-md-8 col-lg-9 nopadding">
<input type="hidden" name="date_attended_min_datepicker">
<script>
$(function() {
if ($.datepicker != undefined) {
$('[name=date_attended_min_datepicker]').datepicker({
showOn: 'button',
buttonImage: '/cmsAdmin/3rdParty/jqueryUI/calendar.gif',
buttonImageOnly: true,
buttonText: '',
dateFormat: 'yy-mm-dd',
onClose: function(date) { // pass the value to the real date field
$('[name=date_attended_min').val(date);
}
});
}
});
</script>&nbsp;Format: YYYY-MM-DD </div>
</div>
</div>
<div class="row" style="margin-bottom: 5px;">
<div class="span3">
<label class="control-label">
Date Attended Max <br>drop off year end </label>
</div>
<div class="span6">
<div class="col-xs-12 col-sm-6 col-md-4 col-lg-3" style="padding: 0 6px 0 0">
<input class='text-input form-control' type='text' name='date_attended_max' value=''> </div>
<div class="help-block col-xs-12 col-sm-6 col-md-8 col-lg-9 nopadding">
<input type="hidden" name="date_attended_max_datepicker">
<script>
$(function() {
if ($.datepicker != undefined) {
$('[name=date_attended_max_datepicker]').datepicker({
showOn: 'button',
buttonImage: '/cmsAdmin/3rdParty/jqueryUI/calendar.gif',
buttonImageOnly: true,
buttonText: '',
dateFormat: 'yy-mm-dd',
onClose: function(date) { // pass the value to the real date field
$('[name=date_attended_max').val(date);
}
});
}
});
</script>&nbsp;Format: YYYY-MM-DD </div>
</div>
</div>


</div>
</div>

<!-- results and buttons row -->
<div>

<div class="pull-left" style="margin: 10px 0;">



<button name="search" value="1" class="btn btn-primary" type="submit">Search</button>

</div>
<div class="clear"></div>
</div>


<input type='hidden' name='_tableName' class='_tableName' value='education_records'>
<div class="horizontal-autoscroll">

<table class="data sortable table table-hover" data-table="education_records" width="100%">
<tr>
<th>Date Attended</th>
<th>Location</th>
<th>Course Name/Activity</th>
<th>Structured (Cat 1)<br><strong>(Total Cat 1): <?php
$sum = 0 ;
foreach ($expiredEducationRecords as $record) {
$sum = $sum + $record['cat_1_credits'];
}
?> <?php echo $sum ?></strong></th>
<th>Unstructured (Cat 2)<br><strong>(Total Cat 2): <?php
$sum = 0 ;
foreach ($expiredEducationRecords as $record) {
$sum = $sum + $record['cat_2_credits'];
}
?> <?php echo $sum ?></strong></th>
<th></th>
</tr>
<?php foreach ($expiredEducationRecords as $record): ?>
<tr>
<td><?php echo date("Y-M-j", strtotime($record['date_attended'])) ?></td>
<td><?php echo htmlencode($record['location']) ?></td>

<td><?php echo htmlencode($record['course_name_activity']) ?></td>
<td><?php echo htmlencode($record['cat_1_credits']) ?></td>
<td><?php echo htmlencode($record['cat_2_credits']) ?>
</td>
<td><a href="education-records.php?del=<?php echo $record['num']; ?>">del</a></td>
</tr>
<?php $totalCredits += ( floatval($record['cat_1_credits']) + floatval($record['cat_2_credits']) ); ?>
<?php $totalCat1 += floatval($record['cat_1_credits']); ?>
<?php endforeach; ?>
</table>
<?php if (!$expiredEducationRecords): ?>
<br/><br/>No Older Education records were found!<br/><br/>
<?php endif ?>
</div>

</form>

I'm now looking at forcing default values using this script  from this forum thread https://www.interactivetools.com/forum/forum-posts.php?postNum=2231209#post2231209

 // load records from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
  ));

  foreach($blogs as $blog){
    echo "Checking record ".$blog['num']." <br>";
    if($blog['title'] == "test"){
      mysql_update('blog', $blog['num'], null, array('title' => 'Default Title'));
    }
  }
  echo "Script Complete";

Glad it worked for you

Jerry

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By daniel - February 14, 2022

Hey dmn,

I think the root of the issue is that when a field is empty, PHP doesn't know how to add it because the value is not numeric. You could probably fix this by setting the field's default to "0" to prevent the field from being truly empty, or by telling PHP to convert it to a numeric value with the intval() function like this:

$sum = $sum + intval($record['cat_1_credits']);

Using error_reporting(0) will also work to silence the error, though I'll note that this will silence all errors on this page, which could make it more difficult to troubleshoot any future issues. 

Let me know if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By dmn - February 14, 2022

Excellent, works great, thank you Daniel.

By dmn - February 15, 2022

Daniel 

using this

$sum = $sum + intval($record['cat_1_credits']);

resolved the empty fields but I have records that have decimals (eg 5.5) where the decimal figure does not get added.

By daniel - February 15, 2022

Hey dmn,

Apologies, I didn't consider decimals! Instead of intval() you should be able to use floatval() in the same way:

$sum = $sum + floatval($record['cat_1_credits']);

Let me know if you have any other issues.

Thanks,

Daniel
Technical Lead
interactivetools.com