Thank you, Mike! That makes so much sense. I really appreciate all of your help!
This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.
The active forums are here.
April 10, 2012 2:16pm
Subscribe [2]#16 / Apr 11, 2012 11:40pm
Thank you, Mike! That makes so much sense. I really appreciate all of your help!
#17 / Apr 12, 2012 1:34am
Well arrrrgh. I’m just not able to get this right. I would be happy to pay someone to look at this for me and see what I’m doing wrong.
Here’s my code for the embedded template (the main template is now exactly as Mike showed me above, except I’m using an underscore for days_joined instead of a dash, because for some reason it doesn’t work with the dash):
{exp:query limit="10" paginate="top" sql="SELECT ct.title,ct.url_title,cd.field_id_21 as summary,cd.field_id_24 as updates_icon
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}I am no longer getting the syntax errors (hooray!), but I am having two problems with the output:
1. I created an entry that is has a days_joined value of 365, and it is not excluding that entry (I assume that these would be hidden from me, since my membership has only been active for a few months). This may be closely related to problem #2.
2. It is not keeping the fields with their proper entry. It is displaying every possible combination of title/summary/updates_icon, rather than showing the title with its related summary/icon.
I don’t expect you to keep answering my questions for free, Mike (you’ve been so helpful!). I would be happy to pay for help. 😊
#18 / Apr 12, 2012 7:57am
Hi Sarah,
Just realized I have an error in the way I’m relating the data and titles tables. Please change the embed query to the following:
{exp:query limit="10" paginate="top" sql="SELECT ct.title,ct.url_title,cd.field_id_21 as summary,cd.field_id_24 as updates_icon
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.entry_id = ct.entry_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}This should do it for you.
Cheers,
Mike
#19 / Apr 12, 2012 11:24am
Thanks! Now the right entries are showing up. Any idea why they would be repeating down the page 44 times? (Is it just a coincidence that my membership could possibly be 44 days old?)
#20 / Apr 12, 2012 11:55am
Try this:
{exp:query limit="10" paginate="top" sql="SELECT ct.title,ct.url_title,cd.field_id_21 as summary,cd.field_id_24 as updates_icon
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.entry_id = ct.entry_id
and cd.field_id_23 <= {embed:days_joined}
group by ct.title,ct.url_title,cd.field_id_21,cd.field_id_24
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}Mike
#21 / Apr 12, 2012 12:02pm
Nope; it’s still doing the same thing. Hmmm.
#22 / Apr 12, 2012 12:13pm
How many entries in this channel have the same day number?
#23 / Apr 12, 2012 12:25pm
I’ve made three entries so far for testing. One to show starting on Day 1, one to show starting on Day 2, and one to show starting on Day 365.
#24 / Apr 12, 2012 12:42pm
Can you make a new test template and put the following code in it please:
{exp:query sql="SELECT ct.title,ct.url_title,cd.field_id_21 as summary,cd.field_id_24 as updates_icon
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.entry_id = ct.entry_id
and cd.field_id_23 <= 1
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}Then call the template in the browser and tell me how many entries get returned.
Mike
#25 / Apr 12, 2012 12:53pm
Just one (the one that’s set to release on Day 1).
#26 / Apr 12, 2012 7:47pm
Did you set the field content to “integer” for the days field in the modules channel fields?
To check it go into the CP and under “Admin > Channel Administration > Channel Fields” click the channel field group you are using for your Module channel. Now click on the days field and scroll to the bottom. Look at the “Field Content” dropdown and make sure it’s set to “integer” and save the form.
Now in your test template you created from the code I gave you try changing the “and cd.field_id_23 <= 1” to “and cd.field_id_23 <= 2” and save the template and call it in your browser. Check to make sure it’s displaying the correct number of records. Now again change it to “and cd.field_id_23 <= 3” and save it then call it in your browser and see what happens.
Cheers,
Mike
#27 / Apr 12, 2012 11:10pm
That’s working great. Thank you. And you were right about the integer setting. That’s correct now. But when I replace the number with {embed:days-joined}, it’s still showing the crazy number of repeated entries.
#28 / Apr 13, 2012 9:43am
Hi Sarah,
Just realized you are looping through all members when you should only be pulling the current logged in member. Change your member query to the following:
{exp:query sql="SELECT screen_name,join_date
FROM exp_members
where member_id = '{member_id}'
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}This will now only show the modules for the current logged in member and will resolve the issue for you.
Mike
#29 / Apr 13, 2012 5:06pm
I think the SQL gods are out to get me. No entries show up when I add where member_id = ‘member_id’. Just to be make sure I haven’t gunked anything up in the communication, here’s the exact code I’m using now:
Main template:
{exp:query sql="SELECT screen_name,join_date
FROM exp_members
where member_id = '{member_id}'
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}Embedded template:
{exp:query sql="SELECT ct.title,ct.url_title,cd.field_id_21 as summary,cd.field_id_24 as updates_icon
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.entry_id = ct.entry_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}Everything is working, except the fact that it’s showing the entries for all the members, instead of just one.
#30 / Apr 13, 2012 7:58pm
Hi Sarah,
Are the entries in the module table specific to each member or are they standard for all members?
Mike