Where clause to exclude categories

10 posts by 2 authors in: Forums > CMS Builder
Last Post: August 16, 2012   (RSS)

By design9 - August 9, 2012

I have a auto feed pulling in the 3 most recent blogs to my home page and I want to exclude two categories. It works fine in my where statement when I want to exclude one category with the following code
list($featuredarticlesoneRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'blogs',
'where' => "category NOT LIKE '%\tThe Daily Post\t%''",
'limit' => '3',
));
but I cannot figure out how to exclude more than one category.
I want exclude The Daily Post category and Parenting Tip of the Week.

Thanks!
April

Re: [design9] Where clause to exclude categories

By Jason - August 10, 2012

Hi April,

You can exclude multiple categories by using the AND statement to put in multiple conditions.

For example:

list($featuredarticlesoneRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'blogs',
'where' => "category NOT LIKE '%\tThe Daily Post\t%' AND category NOT LIKE '%\tParenting Tips of the Week\t%'",
'limit' => '3',
));

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: [design9] Where clause to exclude categories

By Jason - August 14, 2012

Hi April,

Sure. In the code example below, I've made the following assumptions:

1) You're looking for a blog category called "The Daily Post"
2) That value is stored in a field called "title".

Given that, you can get that value like this:

list($featuredarticlesoneRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'blogs',
'where' => "category NOT LIKE '%\tThe Daily Post\t%'",
'limit' => '3',
));

list($blogCategory, $blogCategoryMetaData) = getRecords(array(
'tableName' => 'blog_category_list',
'allowSearch' => false,
'where' => "title = 'The Daily Post'",
'limit' => 1,
));

$category = @$blogCategory[0];

Is this what you're looking for?

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] Where clause to exclude categories

By design9 - August 14, 2012

No, not really. what I am trying to do is use the following code which works fine:

list($featuredarticlesoneRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'blogs',
'where' => "category NOT LIKE '%\tThe Daily Post\t%'",
'limit' => '3',
));

<?php foreach($featuredarticlesoneRecords as $record):?>
<?php if($count>3){$count=1;} ?>
<table width="574" height="30" border="0">
<tr>
<td width="3">&nbsp;</td>
<td width="150">
<?php $count=3;?>

<?php foreach ($record['photo'] as $upload): ?>
<?php if ($upload['hasThumbnail']): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" title="" style="border: solid 1px #4f4f4f;" /><br/>
<?php endif ?>
<?php endforeach ?> </td>
<td width="407"><h2><a href="<?php echo $record['_link'] ?>"><span class="articlehead"><?php echo $record['title'] ?></span></a></h2>
<p><?PHP echo maxWords($record['content'], 25);?>...<a href="<?php echo $record['_link']; ?>">More</a> </p></td>
</tr>
</table><?php endforeach; ?>

I want to pull the title and content of the 3 most recent blogs which this code above is doing. What I want to do differently is pull another photo in that matches the blog title/content that is being pulled from the blog section above. My photo will be coming from another section called blog_category_list. This way I can pull the image of the actual blogger instead of the generic image that is loaded with the blogs section. The blogger image is located in the blog_category_list. So I only want to replace the part of the coding above that pulls in the photo
<?php foreach ($record['photo'] as $upload): ?>
<?php if ($upload['hasThumbnail']): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" title="" style="border: solid 1px #4f4f4f;" /><br/>
<?php endif ?>
<?php endforeach ?>
and update this coding to pull in the blogger photo from the blogger_category_list section. The field of the photo in that section is called blogger photo
<?php foreach ($record['bloggerphoto'] as $upload): ?>

Hope that makes sense.

April

Re: [design9] Where clause to exclude categories

By Jason - August 15, 2012

Hi April,

Okay. How are you associating a record from "blogs" with a record from "blog_category_list"? Is there a unique field between them?

Let me know.
---------------------------------------------------
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] Where clause to exclude categories

By design9 - August 15, 2012

Yes, they both use the same field called category which lists all the categories. That field name is called "category" in both sections which I have pulling in from a category menu using a list field.

April

Re: [design9] Where clause to exclude categories

By Jason - August 15, 2012

Hi April,

Okay, the only problem I see with this is that your category field in blog is a multi select field. If a blog record belongs to multiple categories, how do we know which category to pull from? Do we retrieve all the category records the blog record belongs to?

Let me know.
---------------------------------------------------
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] Where clause to exclude categories

By design9 - August 15, 2012

We do have the list set up as a multi select however it will be very rare that they would assign a blog to more than one category. Our bloggers have their own category and will post blogs to only their category. We used multi-select just in case their ever was a rare instance where it was needed.

So, would it be possible to make it work like this?

Thanks!
April

Re: [design9] Where clause to exclude categories

By Jason - August 16, 2012

Hi April,

You could try something like this. First, we get all the category records and arrange them by title

list($featuredarticlesoneRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'blogs',
'where' => "category NOT LIKE '%\tThe Daily Post\t%'",
'limit' => '3',
));

list($blogCategories, ) = getRecords(array(
'tableName' => 'blog_category_list',
'allowSearch' => false,
));

$blogCategoriesByTitle = array_groupBy($blogCategories, 'title');

Then for each blog record, we attempt to get it's corresponding category record:

<?php foreach($featuredarticlesoneRecords as $record):?>

<?php
$blogCategoryRecord = array();
$blogCategory = @$record['category:values'][0];

if (array_key_exists($blogCategory, $blogCategoriesByTitle)) {
$blogCategoryRecord = $blogCategoriesByTitle[$blogCategory];
}


At the end of this code, $blogCategoryRecord will be the blog_category_list record associated with that given category.

Please note that this code has not been tested, and may need to be modified to work in your specific set up.

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/