Thread

I’m building a site where users can register as a member, and then purchase an online course with Simple Commerce. I thought I had the following query behaving as I needed and only show the course content in each template to members who had purchased the course, but now find that it only works if there is only one course. With the following query and one course, whose item_id is 4, if the member purchased the course, they can view the content of the template. If they have not purchased this course, but have purchased another one, they are redirected to their account page. And if they have not purchased any courses, they are redirected to the page where they can purchase a course.

All fine, but when I add a 2nd course, with item_id of 5, and the member has purchased both courses, the course content for the 2nd course does not display using the same query, but with the item_id changed to 5. Instead the member is redirected to their account page. So, the content of both courses display if the item_id is 4, but neither display if item_id is 5.

{exp:query sql="SELECT * FROM exp_simple_commerce_purchases 
                JOIN exp_simple_commerce_items ON exp_simple_commerce_items.item_id = exp_simple_commerce_purchases.item_id
                JOIN exp_channel_data ON  exp_simple_commerce_items.entry_id = exp_channel_data.entry_id
  WHERE exp_simple_commerce_purchases.member_id = '<?php echo $this->EE->session->userdata['member_id'];?>'"}
                
  {if item_id == 4}
 Content will go here.
{if:else}
   {redirect="courses/account"}
{if no_results}
   {redirect="courses/"}
    {/if}
{/exp:query}

I also tried substituting {if item_id == 4} with each of the following, and get the same results:
{if entry_id == 176}
{if field_id_9 == 'Course 1'}
If I use either of the above code, the content displays, but if I change the id’s to those of Course 2, I am redirected instead of displayed the content.

Could I get some help on what I am missing?
Thanks
Susan

Hi Susan,

you query seems fine. can you check template preference for “PHP Parsing Stage” and make sure if its selected as input.

Bhashkar - I got involved in another project and just saw your response.

Yes, the PHP Parsing Stage is set to Input. Is there any alternative way I could write the query?
Susan

Hi Susan,

i think, there is logical error.

let you have added 2 courses having item ids 4 & 5 and a member had purchased both.

now you are using this query

{exp:query sql="SELECT * FROM exp_simple_commerce_purchases 
                JOIN exp_simple_commerce_items ON exp_simple_commerce_items.item_id = exp_simple_commerce_purchases.item_id
                JOIN exp_channel_data ON  exp_simple_commerce_items.entry_id = exp_channel_data.entry_id
  WHERE exp_simple_commerce_purchases.member_id = '<?php echo $this->EE->session->userdata['member_id'];?>'"}

/* some code */

{/exp:query}

this query will fetch 2 records and will loop 2 times. in first loop, when it finds {if item_id == 4}, will show the contents and in second loop {if item_id == 4} condition will be FALSE because item id will be 5 and control will go into {if:else} {redirect="courses/account"}

and also you are not closing {if} {if:else} properly.

i hope it would help you.

Bhashkar -
You are correct. I now see that it is not item_id 5 that is being ignored, but instead it’s the 2nd item purchased for each member that is ignored. I’ve been trying all day to adjust the conditional without success.

The latest version I tried was

{if (item_id == 5) && (item_id == 4 || item_id == 5)}

Can you give me a suggestion on how to get this to work correctly?

Also, can you give me the correct closing for the query?

{if:else}
   {redirect="courses/account"}
{if no_results}
   {redirect="courses/"}
    {/if}

Thank you
Susan

Hi Susan,

1) why are you checking item_id with their number as you will not able to know the item ids all the times. is it necessary to check item ids with fixed number?

2) please specify when do you want to redirect member to “courses/account” and “courses/”.

3) you conditional tag will be like :

{if item_id == 4}
 Content will go here.
{if:else}
   {redirect="courses/account"}
{/if}
{if no_results}
   {redirect="courses/"}
{/if}

because {if no_results} condition will be check if and only if there is no records fetched from custom query.

i hope it would help you. share your further findings.

Best Regards,
Bhashkar

Hi Bhashkar -
1. I originally used that same query to display the courses that users purchased on their account page. That page used

<a href="http://{field_id_11}">{field_id_9}</a>

for the display, and it works fine. 

Now I need to take the next step, and make sure users who haven’t purchased a course can’t access it. The only thing I could think of was to use the same query and one of the identifying fields to determine if a user should have access to the page material. Since there are only have 2 courses now, I was more concerned with just getting it to work. But if there is another way to do it, or better way, I am open to it.

2.
A. If a user has purchased Course 1, the content should be displayed.
B. If they have not purchased Course 1, but have purchased Course 2, they should be taken to their account page (/courses/account), where there is a link to Course 2.
C. If they have not purchased Course 1 OR Course 2, they should be taken to the Courses page (/courses), where they can make a purchase.

3. Thank you for that closing code.

I really appreciate your helping me with this. I have scoured the forums and haven’t been able to find a similar scenario.

Susan

Hi Susan,

as for now you have only 2 cources and you want getting this to work.

so there we can have very simple solution with a little change in query and also in condition :

{exp:query sql="SELECT exp_channel_data.* FROM exp_simple_commerce_purchases 
   INNER JOIN exp_simple_commerce_items ON exp_simple_commerce_items.item_id = exp_simple_commerce_purchases.item_id
   INNER JOIN exp_channel_data ON  exp_simple_commerce_items.entry_id = exp_channel_data.entry_id
   WHERE exp_simple_commerce_purchases.member_id = '<?php echo $this->EE->session->userdata['member_id'];?>'"}


{if entry_id == [second course entry id] AND total_results == 1} // if member has purchased second course only
 {redirect="courses/account"}
{if:elseif entry_id == [first course entry id] AND total_results == 1} // if member has purchased first course only
 [show the course content]
{if:else} // if member has purchased both courses or any other condition 
 [other stuff here]
{/if}

{if no_results}
 {redirect="courses/"}
{/if}
{/exp:query}

Note : look at check condition with entry_id.

i hope this would help you.

Success!
Hi Bhashkar -
It looks like I’ve finally got this working properly. I wasn’t able to use all of your conditional, because this section

{if:else} // if member has purchased both courses or any other condition 
 [other stuff here]
{/if}

didn’t permit users who purchased both courses to see the content. And also, I found that for users who had purchased the one course, the content was duplicating on the page (a looping problem?)

So here is what I did instead. I used the following query above the template content:

{exp:query sql="SELECT exp_channel_data.* FROM exp_simple_commerce_purchases 
   INNER JOIN exp_simple_commerce_items ON exp_simple_commerce_items.item_id = exp_simple_commerce_purchases.item_id
   INNER JOIN exp_channel_data ON  exp_simple_commerce_items.entry_id = exp_channel_data.entry_id
   WHERE exp_simple_commerce_purchases.member_id = '<?php echo $this->EE->session->userdata['member_id'];?>'"}
                
  {if entry_id == 176 AND total_results == 1} // if member has purchased second course only
 {redirect="/courses/account"}{/if}
{if no_results} // if member has purchased no courses
{redirect="/courses"}{/if}
{/exp:query} 
(page content goes here)

So:
1. If the member has purchased Course 2, AND only purchased one course, he is redirected to his Account page.
2. If he has not purchased any courses, he is redirected to the Courses page.
3. That’s the end of the query. So anyone who does not fit into the above 2 conditions, which will only be those who purchased Course 1, will see the page content.

It seems to be working fine. Is there any reason not to do it this way?

I realize this only works with 2 courses, but additional courses are 6-12 months away, and at that point, I’ll look into a more flexible solution.

Thank you so much for your help. I could not have figured it out without you.
Susan

Hi Susan,

glad to help you. 😊

yes, you are using the simplest way to achieve this. but while increasing the number of courses it will need to extend into more generalized form.

Best Regards,
Bhashkar