displayed records not matching total records

7 posts by 2 authors in: Forums > CMS Builder
Last Post: March 29, 2022   (RSS)

By craig_bcd - March 28, 2022

Hi All -

I have  a section that breaks up articles into different catagories then I display those catagories of articles on different pages.  I have done this a lot on websites but it is not working here and for the life of me I cannot see what is going on.

Below I have identified the components This is where it pulls the records for the page - if I do a record count for that subset of records (see below) the total records lists 15 (which is accurate) but when I do a foreach loop to loop through those records 6 records show up and they are all for category 5 (see attachment for results example).  This code is from a test page which I built to see if I could figure out the issue which is why there are some variables in there that are not normally there. 

I am out of idea's it makes no sense to me.  Any help is appreciated.

Thanks!

This is where it pulls records from the cms on page: 

$blog_where = "2";

$libraryPath = 'cmsb/lib/viewer_functions.php';
$dirsToCheck = array('/home/xanitoshqadmin/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

list($x_newsRecords, $x_newsMetaData) = getRecords(array(
'tableName' => 'x_news',
'loadUploads' => true,
'allowSearch' => false,
'where' => "blog_category = '$blog_where'",
'orderBy' => "`post_date` DESC",
));

This displays the record count just as a check:

$blog_rec = $x_newsMetaData['totalRecords'];
echo ' # records '. $blog_rec;?>

This is the foreach loop on the page:

<?php foreach ($x_newsRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
Blog Category (value): <?php echo $record['blog_category'] ?><br/>
Blog Category (label): <?php echo $record['blog_category:label'] ?><br/>
author: <?php echo htmlencode($record['author']) ?><br/>

<?php foreach ($record['photo'] as $index => $upload): ?>
Upload Url: <?php echo htmlencode($upload['urlPath']) ?><br/>
<hr/>

<?php endforeach ?>

As an FYI - the list field categories are setup like this in the table:

1|Education
2|Operations
3|Safety and Health
4|Awards and Recognition
5|Press Releases

Attachments:

ask-for-cat-2-get-cat-5.JPG 46K

By daniel - March 28, 2022

Hey craig_bcd,

Unfortunately, the issue isn't apparent just from your code snippets here - I have a few questions to help narrow it down:

  • Is what you've pasted below the full code on the page? Since the meta array contains the correct number of records and differs from the length of the records array, I am curious if there's any point after getRecords() where the $x_newsRecords variable could be getting modified.
  • Can you run the getRecords() query with "debugSql" turned on, and copy/paste the output here? Example:
    list($x_newsRecords, $x_newsMetaData) = getRecords(array(
'tableName' => 'x_news',
'loadUploads' => true,
'allowSearch' => false,
'where' => "blog_category = '$blog_where'",
'orderBy' => "`post_date` DESC",
'debugSql' => true,
));​

Thanks!

Daniel
Technical Lead
interactivetools.com

By craig_bcd - March 28, 2022

Hey Daniel - 

Thanks for the help - well I don't know how to explain this but here are the debug results  and i  posted the whole page code below.  Also I took a screenshot of the x_news table in the database and you can see there are records with blog_category = 1

Please let me know if you need anything else. 

thanks!

craig

SELECT SQL_CALC_FOUND_ROWS `x_news`.*
FROM `cms_x_news` as `x_news`
WHERE ('blog_category' = 1)
ORDER BY `post_date` DESC
# records 0

++++++++++++++++++++++++++++++++++

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
// $blog_where = "";
// $blog_where = "1";
$blog_rec = "";

$libraryPath = 'cmsb/lib/viewer_functions.php';
$dirsToCheck = array('/home/xanitoshqadmin/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

list($x_sec_introRecords, $x_sec_introMetaData) = getRecords(array(
'tableName' => 'x_sec_intro',
'where' => "`sn` = 'bed'",
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$x_sec_introRecord = @$x_sec_introRecords[0]; // get first record
if (!$x_sec_introRecord) { dieWith404("Record not found!"); } // show error message if no record found

list($x_newsRecords, $x_newsMetaData) = getRecords(array(
'tableName' => 'x_news',
'loadUploads' => true,
'allowSearch' => false,
'where' => "'blog_category' = 1",
'orderBy' => "`post_date` DESC",
'debugSql' => true,
));
?><!DOCTYPE html>
<!--[if IE 8]> <html class="ie ie8"> <![endif]-->
<!--[if IE 9]> <html class="ie ie9"> <![endif]-->
<!--[if gt IE 9]><!--> <html> <!--<![endif]-->
<head>
<title><?php echo htmlencode($x_sec_introRecord['title']) ?><?php include("core-title-tag.php"); ?></title>
<meta name="description" content="<?php include("core-description-tag.php"); ?>">
<meta name="keywords" content="<?php echo htmlencode($x_sec_introRecord['keywords']) ?>">

<?php include("core-head.php"); ?>

<style>
.nav-tabs > li > a {
font-family: 'Raleway','Open Sans',Arial,Helvetica,sans-serif;
font-weight: 600;
}
.blog-post-item {
background-color: rgba(0,0,0,0.05);
padding: 10px;
}
</style>
<?php include("core-analytics.php"); ?>
<?php
$blog_rec = $x_newsMetaData['totalRecords'];
echo ' # records '. $blog_rec;?>
</head>


<body class="smoothscroll enable-animation">
<!-- wrapper -->
<div id="wrapper">

<!-- Top Bar -->
<?php include("core-top-navigation.php"); ?>
<!-- /Top Bar -->

<div id="header" class="sticky clearfix">
<!-- TOP NAV -->
<?php include("core-main-navigation.php"); ?>
<!-- /Top Nav -->
</div>

<section class="page-header page-header-xs shadow-after-3">
<div class="container">

<h1><?php echo htmlencode($x_sec_introRecord['title']) ?> | <span class="size-16"><?php echo htmlencode($x_sec_introRecord['slogan']) ?></span></h1>

<!-- breadcrumbs -->
<ol class="breadcrumb">
<li><a href="index.php">Home</a></li>
</ol><!-- /breadcrumbs -->

</div>
</section>
<!-- /PAGE HEADER -->
<!-- MAIN INFORMATION -->
<section>
<div class="container">
<?php // echo $GLOBALS["blog_cat"] ;?>
<div id="blog" class="clearfix blog-isotope blog-isotope-3">

<?php foreach ($x_newsRecords as $record): ?>
<!-- POST ITEM -->
<div class="blog-post-item">
<!-- IMAGE -->
<?php foreach ($record['photo'] as $index => $upload): ?>
<figure class="margin-bottom-20">
<img class="img-responsive" src="assets/images/cmsb/<?php echo htmlencode($upload['filename']) ?>" alt="<?php echo htmlencode($upload['info1']) ?> | www.xanitos.com">
</figure>
<?php endforeach ?>

<h2><a href="<?php echo $record['_link'] ?>"><?php echo htmlencode($record['title']) ?></a></h2>

<ul class="blog-post-info list-inline margin-bottom-0 padding-bottom-10">
<li>
<i class="fa fa-clock-o"></i>
<span class="font-lato"><?php echo date("F j, Y", strtotime($record['post_date'])) ?></span>
</li>
</ul>

<p><?php echo $record['summary']; ?></p>

<?php if(!empty($record['content'])): ?>
<a href="<?php echo $record['_link'] ?>" class="btn btn-reveal btn-default">
<i class="fa fa-plus"></i>
<span><?php echo htmlencode($record['link_text']) ?></span>
</a>
<?php endif ?>
</div>
<!-- /POST ITEM -->
<br/>
<?php endforeach ?>
</div>
</div>
</section>
<!-- / -->

<div class="container">
<?php // echo $GLOBALS["blog_cat"] ;?>
<div id="blog" class="clearfix blog-isotope blog-isotope-3">

<?php foreach ($x_newsRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
Blog Category (value): <?php echo $record['blog_category'] ?><br/>
Blog Category (label): <?php echo $record['blog_category:label'] ?><br/>
author: <?php echo htmlencode($record['author']) ?><br/>

<?php foreach ($record['photo'] as $index => $upload): ?>
Upload Url: <?php echo htmlencode($upload['urlPath']) ?><br/>
<hr/>

<?php endforeach ?>

<!-- STEP2a: /Display Uploads -->

<hr/>
<?php endforeach ?>
</div>
</section>


<!-- FOOTER -->
<?php include("core-footer.php"); ?>
<!-- /FOOTER -->

</div>
<!-- /wrapper -->


<!-- SCROLL TO TOP -->
<a href="#" id="toTop"></a>


<!-- PRELOADER -->
<div id="preloader">
<div class="inner">
<span class="loader"></span>
</div>
</div><!-- /PRELOADER -->


<!-- core bootstrap script -->
<?php include("core-bootstrap-script.php"); ?>
<!-- /core bootstrap script -->
<!-- PAGELEVEL SCRIPTS -->

<!-- /PAGELEVEL SCRIPTS -->
</body>

Attachments:

php-myadmin.JPG 104K

By daniel - March 28, 2022

Hey craig,

I think this is a different issue than in the first post - your "where" syntax is a bit off. This:

'where' => "'blog_category' = 1",

Should be this:

'where' => "blog_category = '1'",

This is likely the reason it's returning 0 records at the moment, but in your first post the syntax is correct and it's returning records - just the wrong ones. Try correcting the above issue, but also feel free to send a support request: https://www.interactivetools.com/support/request/

Include the FTP connection details and the location of your test file - I should be able to quickly go in and run a few tests to help troubleshoot the initial issue.

Thanks,

Daniel
Technical Lead
interactivetools.com

By craig_bcd - March 28, 2022

you are right!  see below - still not sure why the #5 records show up in the foreach loop

SELECT SQL_CALC_FOUND_ROWS `x_news`.*
FROM `cms_x_news` as `x_news`
WHERE (blog_category = '1')
ORDER BY `post_date` DESC
SELECT * FROM `cms_uploads` WHERE tableName = 'x_news' AND
fieldName IN ('photo') AND
recordNum IN (64,63,59,58,47,40,38,39,33,31,21,20,14)
ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
WHERE (`num` IN (29,26,1,18))
ORDER BY fullname, username
SELECT SQL_CALC_FOUND_ROWS `members`.*
FROM `cms_members` as `members`
WHERE (`num` IN (29,26,1,18))
ORDER BY user_fname ASC
SELECT * FROM `cms_uploads` WHERE tableName = 'members' AND
fieldName IN ('photo') AND
recordNum IN (1)
ORDER BY `order`, num
# records 13

By daniel - March 29, 2022

Hey Craig,

Thanks for the update - that definitely explains the issue. Glad you were able to figure it out!

Cheers,

Daniel
Technical Lead
interactivetools.com