Hi,
Could really do with some SQL Query help/advice on this.
I’m trying to create a short sidebar list of latest posts from a channel, with each post by a separate author.
Could someone help as regards merging these queries I think I have (sort of) working separately?
format:
screen name
thumbnail
title
this query code creates a list, but with author_id rather than screen name
title works ok, but the thumbnail doesn’t load and the path doesn’t write properly.
{exp:query sql="SELECT t.title, t.author_id FROM exp_channel_titles t
ORDER BY author_id ASC LIMIT 4"}
{author_id}<br />
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:query}
This query code create the path properly and the user name properly but the title is just appearing as {title}
thumbnail doesn’t load.
{exp:query sql="SELECT m.member_id, m.username, m.screen_name, m.avatar_filename FROM exp_members m
WHERE m.group_id='5' "}
{screen_name}
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:query}
I presume I need to use a JOIN but I’m really not that expert with SQL queries.
thumbnail is a field in exp_channel_fields - any idea how I get this into the query?
the filename for the image is in exp_channel_data but it is like this -
{filedir_1}13.jpg
how do I get rid of the
{filedir_1}
bit or create a path?
or should I be using {exp:channel: in there as well ?