SQL Query Option
Development and Programming
How do I use the SQL Query option to select a custom field from another channel and then display the results in another channel?
Examples that did not work:
{exp:query sql="SELECT my_custom_field FROM exp_channel_data WHERE field_id_21 = '1'"}
{my_custom_field}
{/exp:query}
—- returns error message
{exp:query sql="SELECT field_id_20 FROM exp_channel_data WHERE field_id_21 = '1'"}
{my_custom_field}
{/exp:query}
—– returns nothing
{exp:query sql="SELECT field_id_20 FROM exp_channel_data WHERE field_id_21 = '1'"}
{field_id_20}
{/exp:query}
—— returns nothing
I assume the problem is that the custom field name is assigned to the other channel…? There has to be a simple way to do this.
Thanks in advance!
Hiya,
When you say retrieve a field from one channel but then display it in another what exactly do you mean?
You can definitely retrieve a custom field from a channel using the Query module but I’m a bit confused as to what you meant by display it in another channel?
Best wishes,
Mark
Hi Mark,
Here is the long story of what I’m trying to accomplish…
1. I want to have two sets of comments assigned to each entry. (Each comment ‘set’ serves a different purpose, so one set of comments won’t work).
2. To accomplish this, I am basically ‘linking’ an entry from one channel to an entry from another, so I can use the comment set from the ‘twin’ channel entry.
3. The way I planned on linking the two channels is based upon an SQL Query that matches a custom field data to what is in its ‘twin’ entry.
4. My WHERE within the query looks something like: WHERE field_id_21=”{entry_id}” and it works perfectly.
5. What I can’t figure out is how to get the information to display using the EE query tag.
6. I am just trying to avoid doing a php script, but if that is what I will have to do - then … yeah.
So if I’m understanding correctly you are essentially using two entries each with comments in them and want to show all those comments on just one entry page?
If so then you are going to have to use the dynamic=”no” parameter on the second set of comments otherwise they won’t work as they will try picking up the information in the URL and display the wrong comments.
Again if I’m understanding this correctly then what you are going to need to do is to run a query to get the entry_id of the related comment set entry and then use the comment entries tag to display those comments in the first entry.
To do that you are going to need to probably need to run the query inside the first channel entries tag to get the entry_id of the second entry and then embed a template which pulls in the comments.
It might be best however to create a plugin which uses the URL information of the entry that is showing and then have a tag which spits out the entry_id of the second entry where you can then use that to output the comments you need.
Best wishes,
Mark
Hmmm. Sounds like it might be easier to simply create a new table for my second set of comments and just php script the whole thing instead of EE Duct Taping the thing together.
Thanks, Mark.
xoxox
@nnette,
Have you looked at using Relationships? It’s built right into {exp:channel:entries} and sounds like what you are trying to achieve. You would pull in the related entry, and then display its comment information. But I may have over-simplified what you are trying to do.
Cheers,
Dan, I’ll have to think on this option further, though at this point it doesn’t seem like a viable option. Great suggestion though! Thanks. xox
@nnette,
You sound like you have some pretty solid coding chops, have you thought of developing a custom add-on? It would be a sanctioned way to make the changes you need to the ExpressionEngine database. I’m just afraid that if you get too deep into custom scripts and tables, you might take your installation out of support. Plus, if you’re going to do all that work anyway, you might as well leverage the ExpressionEngine APIs 😉
Best of luck, and let us know if we can be of any help in the future. I’m going to migrate this into Development and Programming to keep the discussion going.
Cheers!
Dan,
I’ve thought of that, unfortunately at this point I’m still a bit over my head in php coding. I know just enough to get into trouble.
If the internet blows up in the next few weeks, you will at least know who is responsible…
xoxox
Annette,
I’m wondering if you ever solved this problem of how to display two sets of comments on one page, because I’m trying to do something very similar. For certain entries, I want people to be able to leave either a comment or a haiku. And have all the comments and haikus display on the same page, in different tables.
Unfortunately I know zip about php scripts, but if you figured out a solution to your problem, I’d love to have a look at it to see if I could get it to work on my site.
-Alex