Thread

I am having trouble getting a query to work, and wondered if someone can help me with it.

I have two channels; Movies and Models. The Movies channel has a custom field called Models, which is a multi-select menu allowing you to choose from a list of models.

On the Movies channel I want to display which models feature in the movie, as selected from the multi-select menu. So I did this:

{exp:query sql="SELECT * FROM exp_channel_fields WHERE group_id='2' AND model_site_name='{models}'"}

{model_site_name}

{if no_results}No models in this movie.{/if}

{/exp:query}

group_id 2 is the Models channel fields group, model_site_name is a field within that group and {models} is the list of models selected from the multi-select menu in the Movies channel.

But this just breaks my template and nothing shows. What is wrong?

Hi anthonyweston,

The reason why the query is not working because EE doesn’t store the fields like how you have them in your query “group_id=‘2’” and “model_site_name=’{models}’”. You have to look at the database and exp_channel_fields table to see what the field could be called. EE stores the fields as “field_id_#” where the “#” is some number.

You can look at the db tables using the EE control panel under “Tools > Data > SQL Manager” then click the “Manage Database Tables” button. Or you can use something like phpMyAdmin. I hope that helps.

Mike

Hi Anthony,

It seems like the relationships between entries might help you out here and eliminate the need for a custom query like you’re using. I may be missing what you’re trying to do but review the documentation on Relationships between entries in different channels to see if that fit with what you’re trying to do with Movies and Models.

Hey anthonyweston,

Thank you for posting your question here on the ExpressionEngine forums.

Ryan is dead on with his suggestion. That should work, and it’s much more preferable over running a query.

If you have any questions about Relationships please let me know. I feel like a therapist saying that last bit there. 😊

Cheers,

Thanks for all the responses. Still haven’t been able to work it out.

Relationships wouldn’t work for this as there are more than one Model per Movie, and you can only select one relationship. Also, that wouldn’t allow me to pull some of the fields used on a Models channel entry onto the Movies channel entry.

Hi Anthony,

For when you need more complex relationships than what is baked into ExpressionEngine natively I would strongly recommend checking out Playa - it’s very powerful and well supported by the developer.

Sean