Thread

I’m building a membership site using Membrr, but I can’t figure out the best way to filter entries by join date (i.e. drip content based on when they sign up). For instance on Day 1 of their sign-up period, I want Module 1 to show up; Day 5, Module 2 shows up, etc.

Has anyone done this? I think what I’m trying to do is filter the content based on the member’s sign-up date and the time difference (thanks Membrr forums for at least getting me that far).

Hmm, have you looked at the member join date member parameter - http://ellislab.com/expressionengine/user-guide/modules/member/index.html#join-date

If you’re creating the member account when they subscribe through Membrr this date should be the same as their sign up date.

Thanks…that’s a really good start. So then I would need to take take the join date member paramater, and then set the time difference somehow. So if it’s the join date +1 days, then include [specific entry]. If it’s the join date +5 days, then include [specific entry].

The workflow is starting to make sense, but I’m still not quite sure how to make it work. I feel closer though…thank you!

have you looked at Devot-ee for plugins? have a look at http://devot-ee.com/add-ons/dt-plugin - it can work out days/weeks/months since any set date, you may be able to use that.

That’s a good one. I also saw this one, which looks so close to what I need: http://www.solspace.com/software/detail/date_field_filter/—I would just need to figure out how to integrate it with the date member parameter somehow.

Hi Sarah,

Sorry, I updated the code. This might work for you:

{exp:query sql="SELECT screen_name,join_date
  FROM exp_members 
  order by 1"}
<?php
$joinDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",{join_date}), Date("d",{join_date}), Date("Y",{join_date})));
$currDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",time()), Date("d",time()), Date("Y",time())));
$start_ts = strtotime($joinDt);
$end_ts = strtotime($currDt);
$diff = $end_ts - $start_ts;
echo "{screen_name} ($joinDt) ==> ".round($diff / 86400)." days
";
?>
{/exp:query}

The code above will loop through all the members and display their screen name and the number of days since they joined from today’s date. I hope that helps.

Mike

Wow, Mike! Thanks so much! I do have PHP 5.3+ on my server. So…in order to get it to display the posts for a particular member based on when they joined, I would need to filter the entries using the output from this?

Sorry, I updated the code. Please try the new code I posted.

Mike

It works great for displaying all of the users, their join date, and how many days they’ve been a member. Thank you so much…this is awesome. Maybe this should be obvious, but any ideas on how I use that to filter the content so that it releases certain content on Day 1, more content on Day 2, etc.?

Could I use the stop_before parameter somehow? So if the $diff is less than or equal to 1, stop_before 2010-04-10 00:01 for example. (Seriously…this is probably obvious to everyone but me.)

Hi Sarah,

A few questions:

1) Do you currently have a channel setup for your modules?

2) Are the modules date sensitive or they just go by the number of days a member has joined?

2) If a member is on Day 5 can they access previous modules like Day 1 etc.?

Mike

1) Yes

2) They go by the number of days a member has joined

3) Yes

Not sure if you already have this but I would add a new integer field called “Module Day” to your module channel. This field will store the day number you want the module to be displayed for members when they hit that many days since they joined.

Now all you have to do is embed a template inside the tag where you do your member joined days calculation. Pass the number of days calculated to the embedded template. Your embedded template will look something like this:

{exp:query sql="SELECT ct.title
  FROM exp_channels c, exp_channel_titles ct, exp_channel_data cd
  where c.channel_name = 'YourModuleChannelNameHere' 
    and ct.channel_id = c.channel_id 
    and ct.status = 'open'
    and cd.channel_id = c.channel_id
    and cd.field_id_## <= {embed:days-joined}
  order by ct.title asc"}

{title}

{if no_results}
No results found.
{/if}

{/exp:query}

In the above query you need to find out what filed id is the field you are storing the day number in from the db and replace the “cd.filed_id_##” with something like “cd.field_id_10”. You will also need to change the channel name to your channel and put the name of the parameter you are using to pass days joined to the template where {embed:days-joined}.

Now this will only display the modules that are less than or equal to the days number you pass to the query above.

I hope that helps 😊

Cheers,

Mike

I am so close! (And feeling a little more like a moron every minute. lol)

Okay, so I’ve got my main “updates” template here:

{exp:query sql="SELECT screen_name,join_date
  FROM exp_members 
  order by 1"}
<?php
$joinDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",{join_date}), Date("d",{join_date}), Date("Y",{join_date})));
$currDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",time()), Date("d",time()), Date("Y",time())));
$start_ts = strtotime($joinDt);
$end_ts = strtotime($currDt);
$diff = $end_ts - $start_ts;
?>

{embed="group-tour/drip-updates" days-joined="<?php echo ".round($diff / 86400)."; ?>"}

{/exp:query}

I am totally not sure if that’s the right way to pass the days-joined parameter…I think there’s a glaring error there.

And then I have the embedded “drip-updates” template here:

{exp:query sql="SELECT ct.title
  FROM exp_channels c, exp_channel_titles ct, exp_channel_data cd
  where c.channel_name = 'destinations' 
    and ct.channel_id = c.channel_id 
    and ct.status = 'open'
    and cd.channel_id = c.channel_id
    and cd.field_id_23 <= {embed:days-joined}
  order by ct.title asc"}

<div class="updates-icon">{updates-icon}</div>
<div class="main-content updates">
<h2 class="updates-title">{title}</h2>
<h3 class="sub-title">This destination's itinerary:</h3>
<ul>{summary}</ul>
<a href="http://tourdebliss.com/group-tour/{url_title}" class="updates-link">Go to this destination</a>
<div class="page-break"></div>
  {paginate}
        {if previous_page}
            <a href="http://{auto_path}">Previous Page</a>  
        {/if}
        {if next_page}
            <a href="http://{auto_path}">Next Page</a>
        {/if}
    {/paginate}
</div><!-- .main-content-->

{if no_results}
No results found.
{/if}

{/exp:query}

Is that somewhere close to what you were suggesting? I’m sure I’ve done something wrong, because I’m getting a 502 Proxy Error that says:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘

SELECT COUNT(*) AS count FROM (SELECT ct.title FROM exp_channels c, exp_channel_titles ct, exp_channel_data cd where c.channel_name = ‘destinations’ and ct.channel_id = c.channel_id and ct.status = ‘open’ and cd.channel_id = c.channel_id and cd.field_id_23 <=

Filename: modules/query/mod.query.php

Line Number: 96

Hi Sarah,

Your php code is a bit off in the main template:

{exp:query sql="SELECT screen_name,join_date
  FROM exp_members 
  order by 1"}
<?php
$joinDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",{join_date}), Date("d",{join_date}), Date("Y",{join_date})));
$currDt = Date("m/d/Y", mktime(0, 0, 0, Date("m",time()), Date("d",time()), Date("Y",time())));
$start_ts = strtotime($joinDt);
$end_ts = strtotime($currDt);
$diff = $end_ts - $start_ts;
?>

{embed="group-tour/drip-updates" days-joined="<?php echo round($diff / 86400); ?>"}

{/exp:query}

Then in the embed template you have to include all the fields that you want to display. This doesn’t work like the channel entries tag. We are using the query tag and we have to manually specify which fields we want to pull in order to display them on the page. You will have to look at your db and figure out what each field id is like the “cd.field_id_23” that you added. So your query might look something like this as an example:

{exp:query limit="5" paginate="top" sql="SELECT ct.title,cd.field_id_10 as update-icon,cd.field_id_11 as summary etc…
  FROM exp_channels c, exp_channel_titles ct, exp_channel_data cd
  where c.channel_name = 'destinations' 
    and ct.channel_id = c.channel_id 
    and ct.status = 'open'
    and cd.channel_id = c.channel_id
    and cd.field_id_23 <= {embed:days-joined}
  order by ct.title asc"}

<div class="updates-icon">{updates-icon}</div>
<div class="main-content updates">
<h2 class="updates-title">{title}</h2>
<h3 class="sub-title">This destination's itinerary:</h3>
<ul>{summary}</ul>
<a href="http://tourdebliss.com/group-tour/{url_title}" class="updates-link">Go to this destination</a>
<div class="page-break"></div>
  {paginate}
        {if previous_page}
            <a href="http://{auto_path}">Previous Page</a>  
        {/if}
        {if next_page}
            <a href="http://{auto_path}">Next Page</a>
        {/if}
    {/paginate}
</div><!-- .main-content-->

{if no_results}
No results found.
{/if}

{/exp:query}

Please don’t use the field ids I used as those are just an example to show how your query might look.

What I’m doing in the query here in the embedded template is for each field id I’m assigning a user friendly label. So for example if “cd.field_id_10” is your “updates-icon” field you would write the filed in the query as “cd.field_id_10 as updates-icon”. Now to display this field in your html you would just put {updates-icon} in the html and it will display. If you only put “cd.field_id_10” then you will have to put {field_id_10} in your html to display it which is not very user friendly. It will still work but you won’t know which field it is. You would do this for all your field ids that you want to display just to make it easier on your self.

You also need to add the limit=”” and paginate=”” parameters to the query tag so you can use the pagination tag pair.

Cheers,

Mike