Exclude category in search results
11 posts by 3 authors in: Forums > CMS Builder
Last Post: February 17, 2011 (RSS)
By Thomas - February 8, 2011
I have a list of categories in a drop down menu and call each one as follows. For example:
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=Presentations
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=Techniques
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=News
etc.
I would also like to have a general category that shows all results with the exception of a single category.
For example something like
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=all&except=sponsors
Does this make sense? Is is it possible?
Re: [Thomas] Exclude category in search results
By Jason - February 9, 2011
If you want to show all except one category, you'll need to create a custom where clause.
You can try something like this example:
$where = "";
$allowSearch = true;
if(@$_REQUEST['featured']=="all"){
$allowSearch = false;
$where = " featured != 'sponsors'";
}
list( $articleRecords , $articleMetaData) = getRecords(array(
'tableName' => 'articles',
'allowSearch' => $allowSearch,
'where' => $where,
));In this example code, if the featured variable does not equal "all", CMS Builder will just do it's normal automatic search. If featured does equal "all" we turn off the automatic search and insert a custom where clause that returns all records except records where featured = "sponsors"
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] Exclude category in search results
By Thomas - February 9, 2011
I have added the code
$where = "";
$allowSearch = true;
if(@$_REQUEST['featured']=="all"){
$allowSearch = false;
$where = " featured != 'Sponsors'";
}
list($podcastsRecords, $podcastsMetaData) = getRecords(array(
'tableName' => 'podcasts',
'perPage' => '',
'allowSearch' => $allowSearch,
'where' => $where,
));however, when I search using:
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=all I still get all the Sponsors listings.
The list of sponsors listings can be seen at:
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=Sponsors
I am probably doing something simple wrong. Any ideas?
Re: [Thomas] Exclude category in search results
By ross - February 10, 2011
At first glance, the code should work :). What I'll get you to do is turn on SQL debugging by adding this to your viewer code:
'debugSql' => true,
That will output at the top of the page the entire SQL query that is being run. That should give you some more information on what's happening.
Another thing to try would be to try hardcoding the "where" statement with the query you want just to make sure it actually works :).
How does that sound? Keep us up to date with how you are making out.
Thanks!
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com
Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/
Re: [ross] Exclude category in search results
By Thomas - February 10, 2011
I have added 'debugSql' => true,
to http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php and got the output
SELECT SQL_CALC_FOUND_ROWS * FROM `ivfpodcast_podcasts`
WHERE hidden = 0 AND publishDate <= NOW() AND (removeDate >= NOW() OR neverRemove = 1)
ORDER BY dragSortOrder DESC
SELECT FOUND_ROWS()
SELECT * FROM `ivfpodcast_uploads`
WHERE tableName = 'podcasts' AND
fieldName IN ('story_image','flv_file','m4v_file','zipped_file','playlist_file') AND
recordNum IN (46,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,14,29,28,24,23,22,25,26,21,20,19,18,17,27,16,15,13,12,11,9,8,7,6,5,4,3,2,1)
ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS * FROM `ivfpodcast_accounts`
WHERE (`num` IN (1))
ORDER BY fullname, username
SELECT FOUND_ROWS()Re: [Thomas] Exclude category in search results
By Jason - February 14, 2011
Add 'debugSql' => true and then load the page when featured = all:
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=all
What does it output then? If you look at records where featured = sponsors, is there anything else in the different about those records that would cause them to show up?
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] Exclude category in search results
By Thomas - February 14, 2011
SELECT SQL_CALC_FOUND_ROWS * FROM `ivfpodcast_podcasts`
WHERE ( featured != 'Sponsors') AND hidden = 0 AND publishDate <= NOW() AND (removeDate >= NOW() OR neverRemove = 1)
ORDER BY dragSortOrder DESC
SELECT FOUND_ROWS()
SELECT * FROM `ivfpodcast_uploads`
WHERE tableName = 'podcasts' AND
fieldName IN ('story_image','flv_file','m4v_file','zipped_file','playlist_file') AND
recordNum IN (46,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,14,29,28,24,23,22,25,26,21,20,19,18,17,27,16,15,13,12,11,9,8,7,6,5,4,3,2,1)
ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS * FROM `ivfpodcast_accounts`
WHERE (`num` IN (1))
ORDER BY fullname, username
SELECT FOUND_ROWS()I have left 'debugSql' => true, live on the site so I hope it will help resolving this sooner.
I did put a line in to test (it is now removed)
Category (<?php echo $record['featured'] ?>) and this did show 'Sponsors' for the sections I am trying to remove.
Thank you for all your help so far.
Re: [Thomas] Exclude category in search results
By Thomas - February 14, 2011
I have this in my footer file
list($podcastsRecords, $podcastsMetaData) = getRecords(array(
'tableName' => 'podcasts',
'orderBy' => 'RAND()',
'limit' => '8',
));Maybe it is just easier to split the Sponsors section manually?
Re: [Thomas] Exclude category in search results
By Jason - February 15, 2011
If you could fill out a [url http://www.interactivetools.com/support]2nd Level Support Request[/url] I can take a closer look into this for you.
Thanks
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: [Thomas] Exclude category in search results
By Jason - February 16, 2011
I think we've got this issue sorted out. What was happening is that your featured field was a multi-value checkbox. This mean it's values are stored as a string of tab (\t) separated values. This is how the record was slipping through our query. What we did was change the where clause to look like this:
$where = " featured NOT LIKE '\tSponsors\t'";This will filter out all of the "Sponsors" records.
http://www.ivfpodcasts.com/IVF_Podcasts/Podcastlist.php?featured=all
As for issue with the side bar, try turning off the automatic search function like this:
list($podcastsRecords, $podcastsMetaData) = getRecords(array(
'tableName' => 'podcasts',
'orderBy' => 'RAND()',
'limit' => '8',
'allowSearch' => false,
));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/