Limit number of records returned when certain criteria are met using mySQL

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

By gkornbluth - January 1, 2014 - edited: January 2, 2014

Hi All,

Hope all this makes sense...

On a site I'm developing, members with a membership_level of 1 through 6 can create an unlimited number of 'listing' records.

However, I’m trying to limit the number of records (created by any author with a membership_level of 2) that are shown on a list page, to only the first record where a status field == Available. (all records are sorted by an  'orderBy' => 'createdDate DESC', to show the newest records first)

In addition I need the list page to show all the available records for other levels of membership.

Here’s the ‘where’ I'm using on the list page to limit the overall records that are returned.

'where' => " createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND membership_level LIKE '%2%') )   AND  updatedDate > '$listing_age' AND `status` = 'Available' OR (createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND (membership_level LIKE '%1%'  OR membership_level LIKE '%3%' OR membership_level LIKE '%4%' OR membership_level LIKE '%5%' OR membership_level LIKE '%6%')) ) AND `status` = 'Available') ", 

I’d like to limit the number of records shown for membership_level 2 members in the ‘where’ as well, and tried adding LIMIT 1 to the where (below), but I couldn't get the syntax correct .

'where' => " createdByUserNum IN (SELECT num FROM cms_accounts LIMIT 1 WHERE (account_status LIKE '%1%' AND membership_level LIKE '%2%') )   AND  updatedDate > '$listing_age' AND `status` = 'Available' OR (createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND (membership_level LIKE '%1%'  OR membership_level LIKE '%3%' OR membership_level LIKE '%4%' OR membership_level LIKE '%5%' OR membership_level LIKE '%6%')) ) AND `status` = 'Available') ", 

Any ideas appreciated.

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Daryl - January 3, 2014

Hi Jerry,

What I would do is that I will create only one mysql query for all membership levels then check if the logged in user is a membership_level 2. If yes, get the first record only and display it. For example: 

if ($userMembershipLevel == "2"){
// get the first record only
$listingsRecords = @$listingRecords[0];
}

Hope this helps!

Cheers,

Daryl Maximo
PHP Programmer - interactivetools.com

By gkornbluth - January 3, 2014 - edited: January 3, 2014

Hi Daryl,

Sorry if I confused the issue, whether the user is logged in or not has nothing to do with my request.

It's not the logged in user that I'm going for, it's anyone who visits the site.

Scenario 1) Visitors should only be able to see the first membership_level == 2 record with status == Available, as well as all records for all other users with status == Available

Scenario 2) Visitors should only be able to see the first membership_level == 2 record regardless of status, as well as all records for all other users regardless of status

Thanks,

Jerry

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gkornbluth - January 7, 2014

Sorry, Ross

I was out for a bit (dinner)

That certainly sounds interesting.

would that mean no extra server load or delays in page load?

If so it sounds like an interesting plan

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By ross - January 10, 2014

Hi Jerry

You and I have already spoken about this but wanted to update the thread for anyone else following along.

Basically, what we ended up doing here is creating a plugin that hides all of a free user's records except for the one that was just created or just saved. Pretty simple plugin :).

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