0

I am creating a dropdown menu for a mailing list sign up form for an events company. I would like to give people the option to select a city where the company has had an event in the past or where there will be an upcoming event. To do this, I'm calling all of the event entries and displaying their cities in the dropdown. However, sometimes the company has had events multiple times in one city. How can I remove the duplicates? I have found some answers to questions similar to this where the solution uses php, however I'm having trouble getting that to work in my specific instance. Here is my code for the select dropdown menu:

<select id="MERGE1" name="MERGE1">
    <!--innitial display field is blank-->
         <option value="" selected></option>
    <!--values show in dropdown--->
         {exp:channel:entries  channel="events" status="next event|open|closed" show_future_entries="yes" show_expired="yes"}
         <option value="{event_city}">{event_city}</option>
         {/exp:channel:entries}
</select>

**edit

using an expression engine query solved the innitial problem. however, now i'd like to show cities that appear in 2 different fields and two different channel ID's. Here's what I'm working with so far, which is stripping out the duplicated within the channel ID's but still showing duplicates across multiple channel ID's:

<select id="MERGE1" name="MERGE1">
    <option value="" selected></option>
    {exp:query sql="SELECT exp_channel_data.field_id_1 AS event_city, exp_channel_data.field_id_202 AS event_city2 FROM exp_channel_data 
          INNER JOIN exp_channel_titles ON exp_channel_data.entry_id=exp_channel_titles.entry_id 
          WHERE ( exp_channel_titles.channel_id=1 OR exp_channel_titles.channel_id=29 ) AND (exp_channel_titles.status='next event' OR exp_channel_titles.status='open' OR exp_channel_titles.status='closed') GROUP BY exp_channel_data.field_id_1, exp_channel_data.field_id_202"}
            {if event_city != ''}
               <option value="{event_city}">{event_city}</option>
            {if:else}
               <option value="{event_city2}">{event_city2}</option>
            {/if}
     {/exp:query}
 </select>
2

You can do it with the query module. Try it:

<select id="MERGE1" name="MERGE1">
    <option value="" selected></option>
    {exp:query sql="SELECT exp_channel_data.field_id_1 AS event_city, exp_channel_data.field_id_202 AS event_city2 FROM exp_channel_data 
        INNER JOIN exp_channel_titles ON exp_channel_data.entry_id=exp_channel_titles.entry_id 
        WHERE ( exp_channel_titles.channel_id=1 OR exp_channel_titles.channel_id=29 ) AND (exp_channel_titles.status='next event' OR exp_channel_titles.status='open' OR exp_channel_titles.status='closed') GROUP BY exp_channel_data.field_id_1, exp_channel_data.field_id_202"}
    <option value="{event_city}">{event_city}</option>
    {/exp:query}
</select>

In "field_id_1", the "1" might be replaced with the field id of Event City and put correct channel_id within WHERE condition.

I can see, you are passing parameters: show_future_entries="yes" and show_expired="yes" in channel entry tag. It wouldn't be needed in SQL.

7
  • awesome! that worked! thanks so much. so now i have a follow up question. I actually need to pull from two different fields (i need to pull from 2 places within the table). so I need to add an "and" in there. I tried this, but it gave me sql errors: "SELECT DISTINCT field_id_1 AS event_city AND field_id_202 AS event_city2". Any suggestions? Apr 25 '13 at 18:33
  • it would be "SELECT DISTINCT field_id_1 AS event_city, field_id_202 AS event_city2" i.e replace AND with comma. Apr 25 '13 at 18:36
  • alright, now i need to add a second channel id (the second field is pulling from a second channel), but the comma isn't working. here's what i have "SELECT DISTINCT field_id_1 AS event_city, field_id_202 AS event_city2 FROM exp_channel_data INNER JOIN exp_channel_titles ON exp_channel_data.entry_id=exp_channel_titles.entry_id WHERE exp_channel_titles.channel_id=1, exp_channel_titles.channel_id=29" ... how can i correctly add a second channel (sorry, my sql is obviously not very good) Apr 25 '13 at 18:44
  • I think, you would like to fetch entries from both channels. right! Plz see my answer with updated SQL. Apr 25 '13 at 18:50
  • hmmm first, i had to add a conditional to incorporate both event_city, and event_city2 in the results. however, that removed the duplicates that have the same channel id, but if a city exists in both channel 1 and 29, it still shows it twice... ahhh this is so close! Apr 25 '13 at 19:05
0

You could also use Stash and something like the following. You'd set variables in the first part and then output them when making the select options, using the unique="yes" parameter to filter out duplicates.

{exp:stash:set_list name="st_city_list" parse_tags="yes"}

    {exp:channel:entries  channel="events" status="next event|open|closed" show_future_entries="yes" show_expired="yes"}
        {stash:st_event_city}{event_city}{/stash:st_event_city}
    {/exp:channel:entries}

{/exp:stash:set_list}


<select id="MERGE1" name="MERGE1">

    <!--innitial display field is blank-->
         <option value="" selected></option>

    <!--values show in dropdown--->
         {exp:stash:get_list name="st_city_list" unique="yes"}
            <option value="{st_event_city}">{st_event_city}</option>
         {/exp:stash:get_list}

</select>

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.