Thread

Trying to figure out how to write a custom query that pulls unique entry dates.

Here is what we’ve got:

SELECT DISTINCT entry_date FROM exp_weblog_titles WHERE weblog_id = '8' ORDER BY entry_date

We need to know if we can run a cast or convert function on the entry date field to strip out the time so that we can pull a unique date?

Thanks

Deron, I am a little fuzzy on the details. Do you want only one entry per day? Try this:

SELECT DISTINCT DATE(FROM_UNIXTIME(entry_date)) FROM `exp_weblog_titles` 
WHERE weblog_id = '8' ORDER BY entry_date

and let us know if it comes close to what you are looking for.

Note: Seemed more appropriate in Howto, moved.

Ingmar,

Thanks for the response. Sorry for placing this in the wrong forum, wasn’t thinking.

Anyway, what you gave us wasn’t exactly what we needed, but it helped us figure out what we needed. Here is what we ended up using:

SELECT DISTINCT FROM_UNIXTIME(entry_date, '%M %e, %Y') AS entry_date FROM exp_weblog_titles WHERE weblog_id = '8' ORDER BY FROM_UNIXTIME(entry_date, '%M %e, %Y')"

I’m not a developer at all, but the above is what one of our programmers here at work came up with and it seems to work how we need.

Thanks!

Glad you’re squared away.

Ingmar,

If you’re still hanging around, I’m stuck again with an issue along the same lines as the one above.

Here is the section we’re developing: http://test.ksba.org/boardcast

On this /boardcast main page, it displays the most recent entry date and entries associated with that date on this main page.

Here is the code we’re using for that:

<h1>KSBA Boardcast</h1>
            {exp:weblog:entries weblog="boardcast" limit="1" disable="member_data|trackbacks|categories|category_fields"}
            <h2>{entry_date format="%F %j, %Y"}</h2>
<p>            {/exp:weblog:entries}    <br />
            <ol class="boardcast"><br />
            {exp:weblog:entries weblog="boardcast" display_by="day" disable="member_data|trackbacks|categories|category_fields"}<br />
                <li><a href="http://{title_permalink=" title="{title}">{title}</a>{exp:trunchtml chars="110" inline="..."}{boardcast-body}{/exp:trunchtml}</li><br />
            {/exp:weblog:entries}    <br />
            </ol>

Now, the problem comes in with our sidebar (what we were working on yesterday). Here is the code we’re using to generate the list of dates in the sidebar:

<h2>Boardcast Editions From Previous Dates</h2>
<p>        <ul class="list"><br />
        {exp:query sql="SELECT DISTINCT FROM_UNIXTIME(entry_date, '%M %e, %Y') AS entry_date, FROM_UNIXTIME(entry_date, '%Y%c%e') AS entry_date2 FROM exp_weblog_titles WHERE weblog_id = '8' ORDER BY FROM_UNIXTIME(entry_date, '%M %e, %Y') DESC"}<br />
        <li><a href="/boardcast/entries/{entry_date2}" title="{entry_date}">{entry_date}</a></li><br />
        {/exp:query}            <br />
        </ul>

Each entry date can (and probably will) have more than one entry. We’re only wanting to show one date in the sidebar regardless of how many entries correspond to that date.

So, once you click on one of the entry dates in the sidebar, it should take you to the /boardcast/entries template_group/template. On this template, we need to know how to display entries that correspond to the entry date that was selected from the sidebar. We’ve appended the entry date to the url in this format: “2007626” (as you should be able to see if you click a date). We just cannot figure out a way to show the entries which corresponds to that date?

We want to write a custom query but need to know how to reference that date in the URL to show those entries.

Thanks

Actually, now that I think about it, we can use segment_3 to do this… We’re going to try some things now to see how it goes.  I’ll post back with results.

Yes, please do keep us updated.

OK, we got this to work finally. Our developer here at work was able to put together a custom sql query to accomplish what we need. So if anyone is interested or may need this in the future, here’s what we did:

Here is the main page: http://test.ksba.org/boardcast (“test” is just for testing purposes, if you’re reading this and can’t get the URL to work, remove “test”)

For the main content section on the /boardcast page:

<h1>KSBA Boardcast</h1>
exp:weblog:entries weblog="boardcast" limit="1" disable="member_data|trackbacks|categories|category_fields"}
<h2>{entry_date format="%F %j, %Y"}</h2>
<p>{/exp:weblog:entries}    <br />
<ol class="boardcast"><br />
{exp:weblog:entries weblog="boardcast" display_by="day" disable="member_data|trackbacks|categories|category_fields"}<br />
<li><a href="http://{title_permalink=" title="{title}">{title}</a>{exp:trunchtml chars="110" inline="..."}{boardcast-body}{/exp:trunchtml}</li><br />
{/exp:weblog:entries}    <br />
</ol>

Nothing spectacular here. Just your simple weblog:entries tags so show the newest entries and the most recent date that corresponds with those entries. Each entry (whether it be 5 or 10 or other number, all have the same date)

—————————————————————————————————————————————————

For the sidebar to gather dates and only show one date regardless of how many entries are associated with that date we used a custom sql query:

<h2>Boardcast Editions From Previous Dates</h2>
<p><ul class="list"><br />
{exp:query sql="SELECT DISTINCT FROM_UNIXTIME(entry_date, '%M %e, %Y') AS entry_date, FROM_UNIXTIME(entry_date, '%Y%c%e') AS entry_date2 FROM exp_weblog_titles WHERE weblog_id = '8' ORDER BY FROM_UNIXTIME(entry_date, '%M %e, %Y') DESC"}<br />
<li><a href="/boardcast/entries/{entry_date2}" title="{entry_date}">{entry_date}</a></li><br />
{/exp:query}            <br />
</ul>

—————————————————————————————————————————————————

Any finally for the /boardcast/entries template (used for when the visitor clicks on one of the dates in the sidebar) another custom sql query was used:

{exp:query sql="SELECT * FROM exp_weblog_titles WHERE weblog_id = '8' AND FROM_UNIXTIME(entry_date, '%Y%c%e') = {segment_3} LIMIT 1"}
<h2>{entry_date format="%F %j, %Y"}</h2>
<p>{/exp:query}<br />
<ol class="boardcast"><br />
{exp:query sql="SELECT t1.*, t2.field_id_51 AS 'boardcast-body' FROM exp_weblog_titles AS t1 INNER JOIN exp_weblog_data AS t2 ON t1.entry_id = t2.entry_id WHERE t1.weblog_id = '8' AND FROM_UNIXTIME(t1.entry_date, '%Y%c%e') = {segment_3}"}<br />
<li><a href="/boardcast/article/{url_title}" title="{title}">{title}</a>{exp:trunchtml chars="110" inline="..."}{boardcast-body}{/exp:trunchtml}</li><br />
{/exp:query}<br />
</ol>

It’s all pretty complex if you ask me, but I’m not a programmer/developer, so the sql doesn’t make much sense. Luckily to our developer, it does make sense, so we’re good to go. Hopefully this will help someone if you’re looking for similar functionality.

Thanks for sharing your solution with us.