No. of rows returned and sum of row field

Hi dmn,

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

Thanks!

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

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

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,

Excellent, works great, thank you Daniel.

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.

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,