Filtering one most recent record per category

14 posts by 5 authors in: Forums > CMS Builder
Last Post: June 8, 2010   (RSS)

By Deborah - October 17, 2008

I have a list field titled "category", with three selections. I would like to show the ONE newest entry from each of the three categories on a page.

I can't use the following, because it returns only a record in the category with the most recent entry. Changing the 'limit' to 3, I get the three most recent entries, which can end up being all in the same category.


Header:
<?php
require_once "/home/looks/public_html/cmsAdmin/lib/viewer_functions.php";
list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'limit' => 1,
'orderBy' => 'date',
));
?>



Viewer section of page:

<?php foreach ($blogRecords as $record): ?>
<?php if ($record['category'] != "category-one") { continue; } ?>
<?php echo $record['title'] ?>
<?php echo $record['summary'] ?>
<a href="<?php echo $record['_link'] ?>">more</a>
<?php endforeach ?>

<?php foreach ($blogRecords as $record): ?>
<?php if ($record['category'] != "category-two") { continue; } ?>
<?php echo $record['title'] ?>
<?php echo $record['summary'] ?>
<a href="<?php echo $record['_link'] ?>">more</a>
<?php endforeach ?>

<?php foreach ($blogRecords as $record): ?>
<?php if ($record['category'] != "category-three") { continue; } ?>
<?php echo $record['title'] ?>
<?php echo $record['summary'] ?>
<a href="<?php echo $record['_link'] ?>">more</a>
<?php endforeach ?>






Is this possible?

Thanks, Deborah

Re: [Deborah] Filtering one most recent record per category

By Dave - October 17, 2008

Hi Deborah,

Try using 3 queries, each with a where that limits it to just the category you want:

'where' => " category = 'category-one' ",
'limit' => 1,

Renames your variables as $blogRecords2, $blogRecords3, etc if needed.

Let me know if that works for you.
Dave Edis - Senior Developer

interactivetools.com

Re: [Dave] Filtering one most recent record per category

By Deborah - October 17, 2008

Hi, Dave.

That worked beautifully! I did have to rename the variables, as suggested.

Thank you for your help.
Deborah

Re: [Deborah] Filtering one most recent record per category

By chassa2556 - December 23, 2008

Hi Dave/Deborah

I hope you don't mind me jumping in on this but how would I work in a picture thumbnail with this?

This is working fine

<?php foreach ($previousshowRecords as $record): ?>
<?php if ($record['category'] != "Past Show") { continue; } ?>
<p><strong><?php echo $record['title'] ?></strong><br />
<?php echo $record['summary'] ?><br />
<a href="<?php echo $record['_link'] ?>">See more</a>
<?php endforeach ?> </p>


But I need to put in a thumbnail of the show poster under the title field how could I work that in?

I've tried adding this

<?php foreach ($previousshowRecord['poster'] as $upload): ?>
<?php if ($upload['hasThumbnail']): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>" alt="" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" />
<?php elseif ($upload['isImage']): ?>
<?php endif ?>
<?php endforeach ?>


But its not pulling in the field - any ideas??

Re: [Deborah] Filtering one most recent record per category

By studio-a - June 8, 2010

Hi everyone,

I read your answer Dave and I understand what your are saying to do. Providing we know the category name everything works very nicely.

However, what if we do not know what that category name should equal? For example, the “Category Name” is dynamically being created from another table entry within the CMS? See attached image for CMS reference.

Your help is appreciated!

studio-a

Re: [studio-a] Filtering one most recent record per category

By Jason - June 8, 2010

Hi,

So if I understand correctly, you have a category field that comes from another table in the database, and you want to be able to determine what the most recent record for each category is. Is that correct?

What do you want to use to determine what the newest record is: the date it was created, or the date it was updated?

Let me know and we'll see what we can do.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Filtering one most recent record per category

By studio-a - June 8, 2010 - edited: June 8, 2010

Hi Jason,

You are correct regarding the "category field that comes from another table in the database" but not exactly about our objective.

We are trying to generate a list of Service Categories names followed with a list of Service Title names. There is a total of five (5) Service Categories and unlimited number of Service Title names.

Within the CMS we have a table for Categories and a table for Services. We have a required field with as a drop down list which is populated by the Service Categories table. This is how we thought to associate the Service Titles with the Service Categories. The client needs both of these to be dynamic.

Below is a visual for the text display version and there is an attached image for CSS the layout. Keep in mind, the Service Titles will be associated to only one (1) Service Category and do NOT have to be in any order.

Service Categories 01
Service Title (A)
Service Title (B)
Service Title (F)

Service Categories 02
Service Title (E)
Service Title (C)
Service Title (H)

Service Categories 03
Service Title (G)
Service Title (D)
Service Title (I)

We look forward to your help!

studio-a

Re: [studio-a] Filtering one most recent record per category

By Jason - June 8, 2010

Hi,

I think this may work for you. (Note: you may need to change some names to match what you have in your database or in your .php file)

What we need to do is to create an array of Service Categories, each one having a list of Service Titles inside them.

First we create a list of Category Names based on Category Numbers from the Service_Categories table:

$categoryNumToName=array();
foreach($categoryRecords as $category){
$categoryNumToName[$category['num']]=$category['category_name'];
}


Next we create our array of Service Categories, based on the Service_Title table like this:

$Services=array();

foreach($listingsRecords as $list){
$Services[$list['category']][]=$list['service_name'];
}


The variable $Services, now holds all of the service titles, sorted by service_category.

Now all we need is a nested foreach loop to display the information. For the purposes of an example, I'm outputting it as a nested list, but you can change this to output however you like:

<?php foreach($Services as $key=>$service): ?>
<?php if(is_array($service)): ?>
<ul>
<li><?php echo $categoryNumToName[$key];?></li>
<ul>
<?php foreach($service as $item): ?>
<li><?php echo $item ?></li>
<?php endforeach ?>
</ul>
</ul>
<?php endif ?>
<?php endforeach ?>


Give this a try and let me know if it looks like this would work for you. If you run into any issues, please attach the .php file you're working with so I can take a closer look.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Filtering one most recent record per category

By studio-a - June 8, 2010

Hi Jason,

Thanks for the help, setting things up in an array works perfectly! Although, there is ONE thing that we’re not sure how to handle.

We thought we could control how many Service Titles will be displayed by using the “limit” tag within the query ( 'limit' => '8', ) but this limits the Services Titles AND the Service Categories? We absolutely need to limit the number of Service Titles that are displayed.

Attached is the php file (note: we are using this as an include)

Thanks again for your assistance!

studio-a
Attachments:

menuservices-hm-inc.php 2K