1

Forgive me if I missing something obvious, but I have a need to select channel entries from the EE database, but I want to select those entries based on a value assigned to one of my channels custom fields. In the exp_channel_data table though I have no guaranteed way of knowing which column that data might be in due to how EE adds columns to the table. It could be field_id_27 or field_id_123. I could look in the admin, get the ID and then infer the column name, but that's clunky. Also, I'm trying to use this in a custom plug-in and I can't assume the ID will always be the same as I move the code from EE install to EE install. So what I want to know, and can't find, is what is the recommend method to build a SQL query in EE to be able to use a particular custom field in your WHERE clause?

To put it another way, if my custom field is named my_cutom_field what I want to do is be able to say:

WHERE my_custom_field = 'some value'

I hope this question makes sense.

2

Yes, you can prepare an array which would contain the custom field name and id like:

$fields = array();

$query = $ee()->db->query("SELECT exp_channel_fields.field_id, exp_channel_fields.field_name FROM exp_channel_fields
LEFT JOIN exp_channels ON exp_channel_fields.group_id=exp_channels.field_group 
WHERE exp_channels.channel_name='[CHANNEL NAME]'");

foreach($query->result_array() as $row){
    $fields[$row['field_name']] = 'field_id_'.$row['field_id'];
}

Now within the WHERE condition you can put like: WHERE $fields['my_custom_field '] = 'some value'

| improve this answer | |
  • Thanks. I'm going to try working this method into my solution. What I still have never understood, and maybe this is a whole new question, is why Ellis choose this data model for the custom fields? I personally think I would have used custom field table with a relational table to map that data to the channel data – Adam Christianson Aug 6 '13 at 13:25
1

How about using a JOIN on exp_channel_fields which contains the field metadata?

| 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.