USING 'WHERE" clause to not display Archived list
10 posts by 3 authors in: Forums > CMS Builder
Last Post: December 11, 2014 (RSS)
Hello,
I have had some help with this before but it's archived and now I'm wondering if I ever fully checked it the first time because at that time there were no archived stallions added yet.
I have a list of stallions. Each Stallion has a status list that can be checked by the admin (New, archived, etc.) I don't want any stallions that have been marked as "Archived" to show up on the main list. I've added the where claus but they are still showing
http://www.superiorequinesires.com/stallion-list-breeds.php
// load records from 'stallion_list'
list($stallion_listRecords, $stallion_listMetaData) = getRecords(array(
'tableName' => 'stallion_list',
'where' => "`status` != 'Archived'",
'loadUploads' => true,
'allowSearch' => false, ));
// load record from 'stallion_roster_text'
list($stallion_roster_textRecords, $stallion_roster_textMetaData) = getRecords(array(
'tableName' => 'stallion_roster_text',
'where' => '', // load first record
'loadUploads' => true, 'allowSearch' => false,
'limit' => '1',
)); $stallion_roster_textRecord = @$stallion_roster_textRecords[0]; // get first record
if (!$stallion_roster_textRecord) { dieWith404("Record not found!"); } // show error message if no record found
Belgian/BWP/sBs is the first breed that has "Archived" stallions appearing. I'm attaching the page for reference if needed because I need to get this fixed asap.
Thanks so much!!!! Tina
By Mikey - November 10, 2014
Check out this thread - I think it may have a solution that fits your need.
http://www.interactivetools.com/forum/forum-posts.php?postNum=2231311#post2231311
Hi csdesign,
You're MySQL statement looks like it should work to me, my guess would be that Archived isn't what's being stored in the database. Can you let me know what's being used in the text area for list values? You can find these by going to Section Editors -> Stallion List (modify) -> Status (Modify). Then copy the code that appears in the text area (see attached screen shot) into a post. Also, is this a single select list, or a multi select list?
Thanks,
Greg
PHP Programmer - interactivetools.com
Hi Greg, Thanks for the fast reply! sorry didn't get back to you sooner. I was out sick.
My screenshot is attached - scrolled to bottom to show "Archived" on list
Could the problem be because of the way I listed each breed on the page? I was having issues getting it to display any other way and had to get it online and I think at that point I needed to move on but now I have time to make it right if that's what needs to happen.
I took a screenshot of the code & just attached the page because when I paste it, it does not maintain line breaks.
Thanks so much!! Tina
By gregThomas - November 13, 2014 - edited: November 13, 2014
Thanks for sending me that extra info Tina, I can see what the issue is now. I think it should work if you change your code to this:
// load records from 'stallion_list'
list($stallion_listRecords, $stallion_listMetaData) = getRecords(array(
'tableName' => 'stallion_list',
'where' => "`status` NOT LIKE '%\tArchived\t%'",
'loadUploads' => true,
'allowSearch' => false, ));
// load record from 'stallion_roster_text'
list($stallion_roster_textRecords, $stallion_roster_textMetaData) = getRecords(array(
'tableName' => 'stallion_roster_text',
'where' => '', // load first record
'loadUploads' => true, 'allowSearch' => false,
'limit' => '1',
)); $stallion_roster_textRecord = @$stallion_roster_textRecords[0]; // get first record
if (!$stallion_roster_textRecord) { dieWith404("Record not found!"); } // show error message if no record found
As the status field is a multi select, the values that are chosen are stored in database as a tab separated string. So you have to search that string and check it doesn't include the Archived value.
Let me know if you have any questions.
Thanks!
Greg
PHP Programmer - interactivetools.com
Eureka!!!! Thanks a ton! "Archived" are no longer showing on the list! :)
This solution worked perfectly and now I've been attempting to NOT allow a few more "status" checkboxes but nothing I've done is working. I'm sure you would get a good laugh out of some of my attempts. I have tried adding "and" and "or" and "orwhere" but I'm just missing it. Some of them disable the entire list.
http://www.superiorequinesires.com/stallion-list-breeds.php
// load records from 'stallion_list'
list($stallion_listRecords, $stallion_listMetaData) = getRecords(array(
'tableName' => 'stallion_list',
'where' => "`status` NOT LIKE '%\tArchived\t%'",
'loadUploads' => true,
'allowSearch' => false, ));
This is the one I've been working with. I need to add "North American" & "Fresh Backup" to the existing "Archived"
'where' => "`status` NOT LIKE '%\tArchived\t%'",
Thanks SO much for the assistance!! Tina
Hey Tina,
I think something like the following should work:
'where' => "(`status` NOT LIKE '%\tArchived\t%' AND `status` NOT LIKE '%\tFresh Backup\t%' AND `status` NOT LIKE '%\tNorth American\t%')",
Cheers,
Greg
PHP Programmer - interactivetools.com
By csdesign - December 11, 2014 - edited: December 11, 2014
Worked perfectly!!! I had not tried repeating "status NOT LIKE". You are awesome! Thanks!!!