Query Module not processing all tags from join.
Development and Programming
I have a custom SQL Query, that is joining the exp_channel_titles and exp_channel_data tables. When i’m outputting the template tags none of the exp_channel_data tags are being processed
here is my query string
$sql = "SELECT
exp_channel_titles.title,
exp_channel_titles.url_title,
exp_channel_titles.entry_id,
exp_channel_data.field_id_31,
exp_channel_data.field_id_39
FROM
exp_channel_data
JOIN
exp_channel_titles ON exp_channel_data.entry_id = exp_channel_titles.entry_id
AND exp_channel_data.channel_id = '13' ";and here is my template
<ul>
{exp:query sql="<?php echo $sql; ?>"}
<li>
<a href="http://{title_permalink=portfolio/project/}">_ {project_thumbnail}_ <h3>{title}</h3>_ {project_type}_ </a>
</li>
{/exp:query}
</ul>The {title} is being output fine but the {project_type} just outputs {project_type} for every entry. I have the template php processing turned on and and the parsing stage set to input.
Any help would be greatly appreciated.
Welcome to the EE forums 😊
It’s been a while since I used the Query module so I’m not 100% sure of this statement - BUT:
I think the selection does not replace column names with your custom field short names. Meaning, you might need to use something like {field_id_xx} in your tags instead of {project_type}. The alternative would be to give the column an alias in your query where it applies.
Also of note - you may want to consider not writing the query in PHP tags. Enabling PHP in your template slows the processing down a bit and you can do what you have listed in your code without the PHP.
Eric,
thanks for the help, that solved one issue but brought up another. The “field_id_39” is a link to a thumbnail so now it outputs https://ellislab.com/asset/images/team-photo/4.jpg and doesn’t process the https://ellislab.com/asset/images/team-photo/. Is there something i’m missing that would normally make that tag run if I were using the normal exp:channel:entries?
I’m new to expression engine, first time using it, so I must apologize if this is relatively basic, i’ve been searching around but i can’t seem to find any documentation on this sort of thing.
A little more back story of why i’m using PHP and the custom query… basically this is a loop for an ajax filter. Right now the SQL is hardcoded while testing, but the PHP will be used to pull post data that is being sent by JQUERY to interject variables into the query. I have some extra “like” criteria i’ll be adding to the query once I know everything is being processed correctly. If there is a better way of going about this I would appreciate the tip. Really just trying to learn the system the best I can.
Again thanks for the help, I really appreciate it!
Glad to help!
Let me ask you this - is there something about the data you’re trying to pull in that can’t be done with the exp:channel:entries tag pair? I know you said it’s your first time using ExpressionEngine so I don’t want to assume anything about other methods you may have tried.
This is for a building sorting.. I’m trying to select based off of of 3 fields,
the first would be a type: just 1 field which would store “the type of building” so it would be residential, commercial, etc.
the second would be a combination of 3 fields: length, width, height
i’ll be taking these individually and multiplying together to create an area of the building. The field that will be passed by jquery is an area as a total, and i’ll be selecting based off of it’s if greater then and less then the entry’s total
the third is based on color, which would be 3 fields in the database, siding color, roof color, and trim color. Each of these is a multi-select field. So each may have something like “red, green, blue” when sorting a person would only select one color “red” and i would select all entries where the any of the 3 fields (siding color, roof color, trim color) contains that color “red”.
Is that clear?
I was mainly concerned about the last one not being able to be done by the channel:entries
So i’m positive this isn’t the best way to fix the problem but It works for a work around if nobody has the answer…
basically I just created a https://ellislab.com/asset/images/team-photo/ global variable and now it renders out fine in between my exp:query tag pair.
does any know what would need to be selected from the table to have the https://ellislab.com/asset/images/team-photo/ render correctly without making it a global variable?