Thread

Hi everybody,

I’m working on a glossary for a site. There are not so many entries, so I’d like to display them all at once on one page, but divided with a header for each letter. This wiki entry does pretty much what I, except that it generates only a list of entry titles - ie “{title}”. I cannot figure out how to get it to display my custom field “{definition}” as well.

I’m not so handy with the direct SQL queries, but willing to dig in. My final code should be something like the following:

<dl>
    {exp:query sql="SELECT title, entry_id, url_title AS urlt FROM exp_weblog_titles WHERE weblog_id = '1' AND title LIKE '<?php echo $alphabet;?>%' ORDER BY title ASC"}
    <dt>{title}</dt>
    <dd>{definition}</dd>
    {/exp:query}
</dl>

Can anyone help me figure out what code I need to put in that query that will extract the “{definition}” custom field from my weblog as well as “{title}”?

Thanks very much in advance for any help,
Willhaus

Which method are you using from that wiki article? If 3rd then you are missing:

<?php foreach(range('A','Z') as $alphabet){ ?>

Custom fields are found in table 'exp_weblog_fields' and there you can get 'field_id' then select it from 'exp_weblog_data'.
below I chose my custom field ("extended text" field by default) of ID = 3

{exp:query sql="
SELECT
exp_weblog_titles.weblog_id,
exp_weblog_titles.title,
exp_weblog_data.field_id_3 AS definition
FROM
exp_weblog_titles
Inner Join exp_weblog_data ON exp_weblog_titles.entry_id = exp_weblog_data.entry_id
WHERE
weblog_id =  '1' AND
title LIKE  '<?php echo $alphabet;?>%' 
ORDER BY title ASC
"}
....
{title}
{definition}
{/exp:query}

WOW!

What are you, some kind of superhero? How cool. Thanks so much.

I was getting a “Description: Column ‘weblog_id’ in where clause is ambiguous” error until I changed “weblog_id” to “exp_weblog_titles.weblog_id”. So my working code is now as follows:

<?php foreach(range('A','Z') as $alphabet){ ?>

<h2><?php echo $alphabet;?></h2>
<p>{exp:query sql="<br />
SELECT<br />
exp_weblog_titles.weblog_id,<br />
exp_weblog_titles.title,<br />
exp_weblog_data.field_id_5 AS definition<br />
FROM<br />
exp_weblog_titles<br />
Inner Join exp_weblog_data ON exp_weblog_titles.entry_id = exp_weblog_data.entry_id<br />
WHERE<br />
exp_weblog_titles.weblog_id =  '3' AND<br />
title LIKE  '<?php echo $alphabet;?>%' <br />
ORDER BY title ASC<br />
"}<br />
<dt>{title}</dt><br />
<dd>{definition}</dd><br />
{/exp:query}</p>

<p><?php } ?>

This does lead to one thing: each letter of the alphabet appear whether there are entries under it or not. Would it be too much to ask if you know a slick way to only output letters that have entries? Could I put the <h2> inside the sql query with some logic that says “show the <h2> only if there’s a result from the query”?

Thank you thank you again so very much,
Willhaus

not a superhero just a memeber…they are found here LOL.
yes, sorry that was my mistake with cleaning up the sql and leaving out table name.
For your idea I would suggest using method #2 with embeds where on each embedded template you can run a weblog (vs. method #1 with queries) along with condition ‘if no results’
To eliminate all these queries consider using something as simple as checking if there is something in title returned.
eg.

...
..
ORDER BY title ASC
"}
{if {title} !=""}
<dt>{title}</dt>
<dd>{definition}</dd>
{/if}
{if {title} ==""}
No entries found.
{/if}
{/exp:query}

have not tried this so test it out and expect unexpected 😊

Hi there. This post has helped me quite a bit. I have a list of entries, separated by alpha character using the code provided above. The one thing I need to add is a “misc” section, meaning anything that begins with a number. Here’s what I’m trying:

{exp:query sql="
    SELECT 
    exp_weblog_titles.weblog_id,
    exp_weblog_titles.title, 
    exp_weblog_data.field_id_9 AS link
    FROM 
    exp_weblog_titles 
    Inner Join exp_weblog_data ON exp_weblog_titles.entry_id = exp_weblog_data.entry_id
    WHERE 
    exp_weblog_titles.weblog_id = '12' AND 
    title LIKE '[0-9]%' 
    ORDER BY title ASC"}
  <a href="http://{link}" target="_blank" rel="noopener">{title}</a>
 </div><!-- end case study -->
{/exp:query}

The code isn’t breaking, but isn’t returning the entry either. The title of the entry I’m trying to spit out is “4 Everything”. Thanks for any help! It is definitely in weblog #12, so I am suspecting my sql LIKE isn’t correct.

Thanks!

I would imagine if you are dealing with numbers that you can adjust the php.

<?php foreach(range(0,9) as $number){ ?>

and then

title LIKE  '<?php echo $number;?>%'

But as the alphabet is finite while numbers are not…0-9 doesn’t include ‘10’ so your results will contain (essentially sql LIKE) 1,10….in other words pretty wide range of results depending how many numbers you use in ‘title’.

Thanks for getting back to me! I don’t actually want to run it for each number, but rather to get all entries where the first character is a number as a whole bunch. Is there a way to limit it to the first character in the title?

ugh. it was too late for me to read that last night. all set - thanks!