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 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 craig_bcd - March 29, 2022

Hey Daniel - 

Nevermind - I am too clever for my own good apparently.  I found the issue. The menu system goes out to check and see if there are records to be displayed before it makes the menu option a choice. 

Thanks for your wilingness to jump in with me!

Regards,

Craig

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