0

Using the following Query in my template will not output the title of entries that do not have the ci.filename. BUT when running the SQL in MySQL directly, it does produce the results, including the title field.

Strangely if we output custom fields, e.g. field_id_198 that is also returned, this DOES output.

So it seems Expression Engine is not outputting the title (and some other data like urt_title) when ci.filename is NULL, even though its in the MySQL results, but it is outputting other data.

{exp:query sql="
    SELECT *, ci.filename AS business_logo
    FROM exp_channel_titles ct
    LEFT JOIN exp_channel_data cd ON ct.entry_id = cd.entry_id
    LEFT JOIN exp_channel_images ci ON ci.entry_id = ct.entry_id
    LEFT JOIN exp_members m ON ct.author_id = m.member_id
    WHERE (
        cd.field_id_202 = '{segment_4}'
        OR cd.field_id_202 LIKE '%|{segment_4}%'
        OR cd.field_id_202 LIKE '%{segment_4}|%'
    )
    AND cd.channel_id = '36'
    AND (m.group_id = 1 OR m.group_id = 5 OR m.group_id = 6)
;"}

<div class="row">
    {title}
</div>

{/exp:query}

Removing the exp_channel_images JOIN and field WILL output the title in the results, BUT running direct in MySQL produces the exact same results as the first statement.

{exp:query sql="
    SELECT *
    FROM exp_channel_titles ct
    LEFT JOIN exp_channel_data cd ON ct.entry_id = cd.entry_id
    LEFT JOIN exp_members m ON ct.author_id = m.member_id
    WHERE (
        cd.field_id_202 = '{segment_4}'
        OR cd.field_id_202 LIKE '%|{segment_4}%'
        OR cd.field_id_202 LIKE '%{segment_4}|%'
    )
    AND cd.channel_id = '36'
    AND (m.group_id = 1 OR m.group_id = 5 OR m.group_id = 6)
;"}

<div class="row">
    {title}
</div>

{/exp:query}
  • What $value is set? Try var_dump() to check. – Jonathan Mar 20 '14 at 18:13
  • dont know what you mean, but its fixed in my answer.. looks like title, and url_title is ambiguous as its in two tables. Its just MySQL dealt it with it fine, but EE could not, which was confusing me. But all sorted now by selecting the table directly as opposed to just *. – Laurence Cope Mar 21 '14 at 9:25
1

Adding the specific table to the SELECT ensures it works:

SELECT ct.*
FROM exp_channel_titles ct

There is no other title being output from the other tables, but I can see exp_channel_images does have a title field. I guess EE has issues with the ambiguity even though MySQL outputted them OK.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.