SaranacLake

@kicken, If you had to guess, do you think there is more overhead in running a query or in the data you retrieve? In this case, articles will be from maybe 3 - 10 pages.

While I am doing that, care to give me a hint what the alternative might be? 🙂

@kicken, So can I run a quick query to see if the Article exists before moving forward? For example... SELECT id FROM article AS a WHERE a.slug = $articleSlug; Then if the Article exists, I can do a second check to see if the user has permissions to view the Article. Sound good?

It involves joining 3-4 tables - some with composites keys. I haven't written it yet. Either way, query #1 will return a result set that might be anywhere from 1 to 15 "Category" values. (Since the results will vary, that I why I was thinking query #1 would be in the WHERE part of query #2. Like... WHERE a.category_id IN (query #1 results)

I figured it was a parsing issue. Since I am big on quoting people to give context, that one is pretty important - and even surpasses "Post #"! 🙂 Okay

I think I need a Subquery to solve my latest issue, but am unsure. (Boy am i rusty on SQL!!) Simplified example... Let's say that I have query #1 that joins a bunch of tables (too complicated to post here for now) and it's end result is the following...

Here is a screenshot after I submitted the above post. Notice how the text that I had added AFTER the [ quote ] tags got slurped up into the quote? That is the bug, and it really FUBAR'd my response to you last night since I had multiple quotes from your post then my responses

Above is a [ quote ] from using the "Quote" button. My response: No, I didn't take a screenshot, but let me see if I can recreate it now....

@kicken, Thanks for the response. Last night I sat down and started drawing things out more on paper, and is often the case, I discovered this is more complicated than what I had originally thought. (I have a bad habit of building gold-plated toilet seat ND toilets AND bathrooms!) That being said, I'm not sure what Requinix and I were arguing about is relevant anymore, but I will go back and make sure I don't have the issue that you pointed out, because - in general - I agree that error-messages need to be SPECIFIC - and thus accurate. I am posting a question in the MySQL forum on how to do new query that I think I will need. To be continued, and thanks again!

This is a bug I have encountered before and is rather infuriating as it trashed lots of typing and formatting. If you quote someone (or add your own quote) and then add some text outside the quote, everything gets slurped up into the first quote which creates a real FUBAR at times. Example: https://forums.phpfreaks.com/topic/310084-article-entitlements-for-free-articles/?do=findComment&comment=1574709 I added a quote tag around my use-case so i could format key parts using color which I cannot do using the [ code ] tags. The text I added below this got slurped up into the quoted use-case. And prior to that it slurped up quotes from Requinix and then my responses created a couple layers of nested quotes which is NOT what I told it to do. Had to delete my entire post and strat over, and as you can see at the end of the link above, even my 2nd attempt got FUBAR'd as well. Infuriating. You have been warned...

My use-case above clearly addresses this issue. Here it is again...

When a user clicks on an Article link, I am trying ascertain whether or not they have rights to see the Article BEFORE I go grab the Article from the database. (Why waste time and resources grabbing an Article is the user is not allowed to see it? Unless I wanted to show the first paragraph as a teaser maybe...) Maybe you missed what I said here... https://forums.phpfreaks.com/topic/310084-article-entitlements-for-free-articles/?do=findComment&comment=1574685 (Yet another need for a Post #) 😉 (No outer join - that was an earlier post and a bad idea.)

Actually, I think you are over-simplifying things... 1.) My design is NOT binary. A "Platinum" Member will have access to Articles that a "Gold" Member might not have access to. 2.) There is also the issue that if a Member is not logged in, then I don't know what Membership Plan they have, so I have to treat them as an unregistered Guest. 3.) Some articles might be viewable by everyone (i.e. Guest, Silver, Gold, Platinum) and other Articles will be viewable by other combinations (e.g. Silver, Gold, Platinum or Platinum-only). 4.) Also is the the fact that i am trying to be efficient with my code and not query the database TWICE - once to see if the article is free and a second time to see which entitlements apply. What was wrong with the solution I posted above? (I thought it was consistent with what you said, while tying loose ends together.)

How about this... 1.) I eliminate my MEMBERSHIP_PLAN_VERSION table because that is overkill for setting Entitlements for Articles. (This would make more sense when I give Webpage Entitlements.) 2.) Take this general coding approach... Use-Case --------- - User clicks on an Article link... - "article.php" launches. - Script grabs "Article-Slug" from URL. - Script checks to see is user is logged in. - IF ($loggedIn) THEN - Query for member's "membershipPlanID" - Set $membershipPlanID ELSE - Set $membershipPlanID = SomeFreeCode ENDIF - Run this query to see if user has proper Article Entitlement... SELECT e.id FROM article_entitlement AS e INNER JOIN membership_plan AS mp ON mp.id = e.plan_id INNER JOIN article AS a ON a.id = e.article_id WHERE (mp.id = $membershipPlanID AND a.slug = $urlArticleSlug) - IF Article is found THEN Display Article ELSE Display error "This is a premium article..." ENDIF Similar to what @requinix said above, then I would then have to assign all "free" Articles to a "Membership Plan" called "FREE". Thoughts?

Like most things, there are probably a couple of different ways to solve this problem. I am trying to write some pseudo-code now, but am also thinking that an outer-join might be helpful...

Maybe some sample data would help better explain where I am stuck... MEMBERSHIP_PLAN +------+-----------+ | ID | Name | +------+-----------+ | 1 | Silver | | 2 | Gold | | 3 | Platinum | +------+-----------+ MEMBERSHIP_PLAN_VERSION +----+---------+------------+------------+------------+ | ID | plan_id | version_no | start_date | end_date | +----+---------+------------+------------+------------+ | 1 | 1 | 1 | 2020-01-01 | 2020-02-29 | | 2 | 2 | 1 | 2020-01-01 | 2020-02-29 | | 3 | 3 | 1 | 2020-01-01 | 2020-02-29 | | 4 | 1 | 2 | 2020-03-01 | 2020-06-30 | | 5 | 2 | 2 | 2020-03-01 | 2020-06-30 | | 6 | 2 | 3 | 2020-07-01 | 2020-12-31 | +----+---------+------------+------------+------------+ ARTICLE_ENTITLEMENT +----+---------+------------+------------+ | ID | plan_id | version_no | article_id | +----+---------+------------+------------+ | 1 | 1 | 1 | 71 | | 2 | 2 | 1 | 71 | | 3 | 3 | 1 | 71 | | 4 | 1 | 2 | 71 | | 5 | 2 | 2 | 71 | | 6 | 2 | 3 | 71 | | 7 | 2 | 3 | 74 | +----+---------+------------+------------+ ARTICLE +-----+-------------------------------+-----------------------------+ | ID | slug | Title | +-----+-------------------------------+-----------------------------+ | 71 | using-indexes-to-tune-mysql | Using Indexes to Tune MySQL | | 72 | working-with-arrays-in-php | Working with Arrays in PHP | | 73 | preventing-sql-injection | Preventing SQL Injection | | 74 | file-handling-ing-php | File Handling in PHP | | 75 | using-var-dump-to-debug | Using var_dump to Debug | +-----+-------------------------------+-----------------------------+ Let's say that I want Article (id=73) to be available to everyone, including Guests, Paid Members that aren't logged in (aka Guests), and Paid members who are logged in regardless of which "Membership Plan" they have. In my other thread (and above to a lesser degree), I explained how I would take the $memberID and see if a user has an "entitlement" for a given Article. But since Article (id=73) is free, there are no entitlements. So how would I change my table structure or my PHP code to handle this? (Again, I was planning on having a function at the top of my "article.php" script that always looks for the $memberID and then calls my checkArticleEntitlement function. Not sure if my question is making sense?

I don't see how that answers my question.... Maybe I should have posted in the PHP forum? I went into greater detail in my last thread, but nobody responded, so I tried summarizing here.

Earlier this week i created a table layout to incorporate "entitlements" into my website, which looks like this... MEMBER >|-------||- MEMBERSHIP_PLAN -||-------|< MEMBESHIP_PLAN_VERSION -||-------|< ENTTLEMENT >0-------||- ARTICLE MEMBERSHIP_PLAN - id (PK) -name MEMBERSHIP_PLAN_VERSION - id (PK) - plan_id (UK1)(FK) _ version_no (UK1) - start_date - end_date ARTICLE_ENTITLEMENT - id (PK) - plan_id (UK1)(FK1) - version_no (UK1)(FK1) - article_ID (UK1)(FK2) ARTICLE - id (PK) - slug (UK) - title - body In order to determine if a Member can view "premium" content, I have decided to.... 1.) Take the logged in Member's $memberID and run a query (function) to get their "Membership Plan" (plan_id) 2.) Call another query/function like below to determine if the are entitled to view the "premium" Article... SELECT e.id FROM membership_plan_version AS mpv INNER JOIN article_entitlement AS e ON (mpv.plan_id = e.plan_id) AND (mpv.version_no = e.version_no) INNER JOIN article AS a ON (a.id = e.article_id) WHERE ((CURDATE( ) BETWEEN mpv.start_date AND mpv.end_date) AND ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug))); Now onto my problem... How can I handle cases where an Article is "free" and therefore everyone is entitled to view it regardless of their "Membership Plan" or even if they are just a Guest?? My ARTICLE table obviously has to have *every* Article in it, since that is where the content resides! But if my PHP script called a function/query like above, then for most of my Articles, there will NOT be a corresponding "Membership Plan" on the left to match to, and so free Articles would never appear. Should I create a dummy MEMBERSHIP_PLAN record called "Free", and also make a dummy MEMBERSHIP_PLAN_VERSION record called "Free v1"? Or something else? I would say that 60% of my Articles are free and therefore should be accessible to everyone (i.e. Members and Guests), but the other 40% is restricted to various levels of "Membership Plans". Hopefully my design above is still solid and just needs to be tweaked, but I'm not really sure how to tackle this issue. Thanks i advance!

@gizmola, I responded to your very long post with some equally long responded. No comments or answers to my questions?

No comments on the solution I described in my last post, @gizmola and @Barand? I thought my solution used some of Barand's early comments, but tightened things up.

If they did, it would be no sin. Sorry my comment came across the wrong way.

You read too much into what I said, and I could have worded it better. Put another way, if you run a business your goal is to have a working system and make $$, which apparently your forum software accomplishes. That doesn't mean it is perfect - which it isn't - or that it will satisfy every user's need (e.g. Post #). That's all. At least you guys took into consideration my request for a Post #. Thanks.

I *NEVER* said anything about anyone being greedy! I was saying that if the software used on PHPFreaks helps the owner(s) to make $$$, then it is likely "good enough" from their perspective, even if it doesn't have a "Post #". Nothing wrong said or implied there...

Nope, it's there. See below... MEMBER >|-------||- MEMBERSHIP_PLAN -||-------|< MEMBESHIP_PLAN_VERSION -||-------|< ENTTLEMENT >0-------||- ARTICLE At this time, I just have a single field that stores whatever the Member's "Membership Plan" is at any given moment. (Although I am thinking it might be nice to have a history table of sorts so that I can easily keep track of what different Membership_Plans a Member has over time, but that is immaterial to this discussion.) A "lookup" table table is always modeled as a One-to-Many where the"One" comes from the lookup table, and the "Many" side is on the parent table (e.g. "Member"). And that was my point above, that - as i recall - it makes it hard to have one query where you join "MEMBER" to "MEMBERSIP_PLAN" plus the other tables. Yes, he is very helpful! How about this... When a Member is logged in, I always retain the "memberID" in my SESSION. So I could have a PHP function called: checkArticleEntitlement($memberID){ } I would pass in the $memberID which is always known for logged in users, then my query (pseudocode) might look like this... SELECT e.id FROM membership_plan_version AS mpv INNER JOIN entitlement AS e ON (mpv.plan_id = e.plan_id) AND (mpv.version_no = e.version_no) INNER JOIN article AS a ON (a.id = e.article_id) WHERE ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug)) Then, if COUNT>0 then that means the Member is "entitled" to view the Article, otherwise I display a gracious message and encourage them to upgrade!! 😄 If they are eligible, then i would query the ARTICLE table for the record where article.slug = $artcileSlug (which comes from the URL) (This is where a Post # would have been nice...) So to my question above, I asked this... Put another way, which impacts the database server more.... "Going wide" (i.e. returning lots of fields in a record OR "Going deep" (i.e. having to search a table for a record twice)?? For example, in my SQL above, I chose NOT to return the entire Article record UNTIL I know that the user is eligible to view it, so there I splits things up into two queries. Thoughts on this, @gizmola?

@gizmola, Wow! Your long response makes me teary-eyed for the days of past when people actually talked and had conversations of substance on the Internet?! (Excuse me while I get a tissue...) Okay. What am I doing? I hope to get there someday soon, but I cannot justify that, nor do I need it now. Interesting tangent that I have been wondering about... If I had to guess, based on the website that I am building, I would have said that 85% of my resources will go to serving up HTML/PHP web pages and the images on them, and that database resources will be minimal until if/when I have a really rocking website. After all, my database only hold things like user credentials, member profiles and preferences, and my articles and other content. (Images are relegated to a directory.) Of course I have no real metrics to go off of yet, but I stand by my guesstimates above. Can you explain to my why you feel/know that the paradigm is flipped, and that database resources are much more intense than webserver resources? Right, that is what I said above and am guessing will be the case. Well, if I wanted to spend the $$$, my webhost will gladly sell me a configuration with multiple dedicated webservers and a dedicated database server. I have no clue how to answer that.... My webhost offers load balancers and does all of the configuration. (Not sure if they offer that for VPS's - I'm guessing you need to buy dedicated servers for that. Either a shared or dedicated load balancer if I upgraded. I am banking on 8GB of RAM being enough on my VPS for now. (And IF I could just ever finish getting this stupid website coded, THAT would be the true miracle?!) As an Analyst by day, I agree 110%. The problem that I face - and always have - is that I can't seem to keep up with technology, let alone the world. (Maybe all of your gurus here can, but I find it nearly impossible to master HTML, CSS, PHP, responsive design, MySQL, SQL, database design, website design, everything related to web hosting, security, and a bunch of other things. Most people think I am nuts trying to be CEO and janitor, but to me that is the only way to be a true entrepreneur and a true business/technical guru. (But every year the world goes faster and faster, and it's getting nearly impossible to keep up?! And now you pose some VERY good questions, for which I have no knowledge or solutions. (I hope this isn't another, "Well, go back to studying new topic XYZ, and in 2-3 years you will have the basics down!" If I don't get my cod-base wrapped up and my website up by say summer 2020, then I should just hang up my hat and close shop because it's taking too long. One reason it's taking me so long is I love to get into these intellectual challenges/thought-games about how to build a better mouse-trap, but I was kind of oping I have done enough of that and if I can just finish the e-commerce module for my website, then I will have enough for v1.0 You are starting to make it sound like that isn't the case... I'm not really sure what that means, but I have often wondered for v2.0 - assuming you don't crush me in this thread?! - that maybe i should learn how to do more advanced database work in MySQL for example... Currently I would write a PHP script that reads like a linear use-case because that is my procedural background - not modern, but I'd argue it still works just fine. And in a script, say "article.php" like we have been discussing, i would have a long block of code where my PHP sets up and runs a MySQL query and then takes the results and does something (e.g. display an article, determine if a member is eligible to see the article, etc.) I *think* the way a high-priced oracle developer would approach things is to store the "business logic" in either a Trigger or a Stored Procedure (written in PL/SQL) and then when so event happens the Trigger would fire or maybe you'd just pass arguments to the Stored Procedure and it would run on the database and return just the results set. Is that sorta what you mean? To me, the benefit of that is again 'component architecture" where you "write once, and run many times". (I know that I am a serial offender for writing code/queries in my PHP 100 times instead of maybe creating a PHP function or a database object (e.g. trigger, stored procedure) and reusing them.) But I wouldn't be here asking for help if I was that smart?! Yeah, that is way out of my league!! Well, I know that the Internet - as I knew it - is dying/dead. (If I wanted to sell my soul and get on Facebook/Twitter/Slack then it might be better, but I refuse. Plus you will NEVER see people have long, drawn-out, dare I say "intelligent", conversations on social media like on a good OLD-FASHIONED user-forum like PHPFreaks!!! That being said, I would be really *nervous* about switching to PostgreSQL and getting stranded - although I do see you guys have a forum here. As far as the product, yes, I think PostgreSQL is still growing. Unless you or other experts here show me differently, I am MUCH more concerned about the webserver aspect of my webhosting situation, versus what you say about a potential need for a dedicated database server early on. Over Christmas i was working on a photo website to share Holiday photos, and I quickly learned how things like photos can kick your *SS!!! (I think I am much more concerned about re-tooling my website to use something like AMP versus running off to buy a dedicated database server.) If I ever "go live", and my v1.0 survives, then what I use for a database in v2.0 will largely be due to which environment lets me do fairy robust database concepts like Check Constraints, Triggers, Stored Procedures, etc. Now you see why I feel like I will never get this business going... How can one person manage all of this stuff?! I have been looking into backups, but I really have no clue how you would do a DATABASE recovery on a bus website. (My simpleton solution is to have WHM/cPanel do a sh*tload of backups (e.g. hourly) and hope that that along with running RAID is enough to cover your butt. But if it was Black Friday and I have 500 concurrent people checking out and my database and/or website went down.... I perish the thought?! This is awesome advice, but honestly getting to a point where it's asking a Honda to tow a semi-trailer. A lot of the things you are mentioning here - while all VERY valid - are things that I watch entire teams and departments at my client's sites at work struggle to get down, and they have 5, 10, 50, 100 people working on all of these things. There is no way little ol' me can do all of these things any time soon. About the best I can do is 1.) Design solid, scalable, flexible solutions to the best of my ability, and 2.) work iteratively like we do using Scrum/Agile. Tough question. Again, my biggest challenge is trying to finish this website which has taken me years... (I certainly have bitten off more than i can chew with my subscription and e-commerce modules, but IF I can get them done, that is how I can really start making some $$$.) My web host is not only pretty big, but here in the U.S. and always there 24/7 to help. They offer off server backups on my VPS now, and if I had a dedicated server(s), then I'm sure they offer as much as I can afford. (Which is limited until I get my site up and hopefully start making some money?!) I think I will be okay with back and data recovery solutions - I think they challenge is how quickly can "I" recover my website or database if it died on a busy day/night. And based on things you said above, I probably do NOT have robust enough solutions to prevent someone who is registering or checking out from when my website goes down to not suffer from a broken transaction (figuratively). Again, one step at a time.... The simplest way to describe what I am doing - and without showing too much of my cards - is a combination of the NY Times and a modest e-commerce site. There will be a fair amount of free content, but like the NYT or Wall Street journal, the good stuff requires a paid subscription. In addition, there is lots of other content (e.g. books, guides, etc) that you can buy in my online stores - most of it online content, and probably some of it printed. Good advice, but I have no budget for that. More importantly, there is no business justification. For example, let's say i took and was able to implement *everything* you advise here, what is to say that other than a few crickets, anyone would even come to my website? (I once spent a month building a website for people I knew to check out some cool videos and photos from the area, and I had like 3 people even visit the website. After that, I have been humbled and now see that what is "cool" to me the rest of the world might very likely not give a flip about?!) I am definitely making mental notes here, and I REALLY appreciate all of your advice - you definitely sound like you know your stuff! I am still think that i will need more upfront as far as two webservers than help on the backend, but I could be totally wrong. How much do you think going from a VPS with 8GB of RAM to a dedicated server with say 8GB RAM changes things? (I was told buy a seemingly knowledgeable tech to not go above 8GB on my VPS, but if I needed more power to switch over to a dedicated server at that point. Of course I would love to have 2 webservers and 1 dedicated database server, but that'd likely run me $500/month and not even having a website online, let alone customers, it's hard to think that far ahead. If I can afford 2 dedicated servers, I think they have a plan where their shared hardware load-balancer isnt that much more. I don't know, what kind of site would you say i have? Better yet, what are some examples of "transactional" sites? (Is PHPFreaks a "transactional" website?) I described earlier how and where I use MySQL. Since my website uses no javascript, and have no SH*T on it (e.g. WordPress, add-ons, advertising, marketing pop-up crap, etc), my hope is that the only bottle-neck is serving up images. (And I think AMP can solve most of that.) How much energy can logging into your account or serving up a 4 page article be on my database? (Serving up four 300KB photos could cause issues, though.) ** Here is hoping this mega-long reply doesn't break your forum software?! **