USING 'WHERE" clause to not display Archived list

10 posts by 3 authors in: Forums > CMS Builder
Last Post: December 11, 2014   (RSS)

By csdesign - November 10, 2014

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

By gregThomas - November 11, 2014

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

Greg Thomas







PHP Programmer - interactivetools.com
Attachments:

CMS Builder.png 81K

By csdesign - November 13, 2014

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

Greg Thomas







PHP Programmer - interactivetools.com

By csdesign - November 13, 2014

Eureka!!!!  Thanks a ton!  "Archived" are no longer showing on the list! :)  

http://www.superiorequinesires.com/stallion-list-breeds.php

By csdesign - December 11, 2014

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

By gregThomas - December 11, 2014

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

Greg Thomas







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!!!