Thread

I’m using Solspace’s user:search tag.  It doesn’t have the ability to bring in select list values so I’m using the query module.  I have successfully formed my query, but could still use a little help.

How do I make it so that each list value returns in a an option value tag?

<select name="local_areas">
<option value="0">Select One</option>    
{exp:query sql="SELECT exp_member_fields.m_field_id AS field_id, exp_member_fields.m_field_list_items AS local_areas
FROM exp_member_fields
WHERE exp_member_fields.m_field_id = '5'
;"} 
<option value="{local_areas}">{local_areas}</option>
{/exp:query} 
</select>

HTML that’s rendered

<select name="local_areas">
<option value="0">Select One</option>    
 
<option value="
Bellingham
Bremerton
Ellensburg
Olympia
Seattle
Sequim
Snohomish Valley
Spokane                       
Tri–Cities
Vancouver
Wenatchee—North Central WA
Yakima
British Columbia
Idaho
Oregon
Other">
Bellingham
Bremerton
Ellensburg
Olympia
Seattle
Sequim
Snohomish Valley
Spokane                       
Tri–Cities
Vancouver
Wenatchee—North Central WA
Yakima
British Columbia
Idaho
Oregon
Other</option> 
</select>

Thanks for the assist!
jules

[Mod Edit: Moved to the Community Help forum]

Hi Jules,

If all the select options are stored in one table row then you will probably need to use some PHP on Output in this template. It looks like the options are returned with a linefeed between each one so perhaps you can use the PHP explode function to get each option and then use a foreach loop to output each one surrounded by the proper HTML. I’ll move this thread to the Community Help forum for you.

Cheers

Greg

are there separate php variable names for custom member fields? If so how do I find out what they are?

jules

Hi,

We also use the Solspace Search module and it’s not difficult to get it working. See example. Something like:

<select name="capacity-from"  id="capacity-from" class="select50">
{exp:query sql="
SELECT DISTINCT d.field_id_25 
FROM exp_channel_data AS d, exp_channel_titles AS t 
WHERE t.site_id = 1
AND d.entry_id = t.entry_id 
AND t.status ='open'  
AND t.channel_id = 2 
ORDER BY d.field_id_25 ASC"}
{if field_id_25 =="0"}
<option value="">--- Select Capacity ---</option>
{if:else}
<option value="{field_id_25}" {if super_search_capacity-from =="{field_id_25}"}selected="selected"{/if}>{field_id_25} Kg</option>
{/if}
{/exp:query}  
<option value="">All</option>
</select>

Here you can see it in action, click at the right on “Zoeken” or “Uitgebreid Zoeken”.

http://www.linde-occasions.nl/gebruikte-heftrucks

Greetz,
Ralf

Hello

Because the “Community Help” forum doesn’t always pan out I turned to experts-exchange.com for some php/mySQL help.  I chose a provocative title Looking for PHP expert to help formulate explosion and foreach loop and included the CMS info, my code as well as the browser source code it produced. I attracted an individual rated as a “Genius” in PHP and mySQL.  His first response was to post this

$sql="
SELECT exp_member_fields.m_field_id
AS field_id, exp_member_fields.m_field_list_items
AS local_areas
FROM exp_member_fields
WHERE exp_member_fields.m_field_id = '5'
";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
    echo '<option value="' . $row['field_id'] . '">' . $row["local_areas"] . '</option>' . PHP_EOL;
}

I explained that the Query Module Tags is limited to sql=  and that it won’t work with $ before the sql=

After a bit more of explaining that I was trying to get around a limitation of the user search templating system he questioned why I would choose a CMS with such limitations and signed off on any further assistance.  I must say I was quite surprised since I was able to successfully use the query tag to bring in the info I needed, I had thought that using php it would be possible to populate the html correctly.  Since he is supposedly knowledgeable in this area I’m wondering if he is correct.

I’d share the link to experts exchange, but you have to be a member to see posts.

Greg previously stated

... you will probably need to use some PHP on Output in this template. It looks like the options are returned with a line feed between each one so perhaps you can use the PHP explode function to get each option and then use a foreach loop to output each one surrounded by the proper HTML.

I believe that you can always find a way to get around an issue, so I’m finding myself between a rock and a hard place.  I really believe there’s a way around this, but my knowledge of mySQL and PHP are holding me back and I’ve never had experts exchange bail on my before.  I’m really hoping for some sort of feed back here.  Anybody have any ideas or opinions?

Thanks
jules

do you use EE1 or EE2? Is the custom member field called “local_areas”?

@ ikiro
Thank you for your post. 
I’m actually using Solspace User module and implementing the exp:user:search tag.  The crux is that EE custom member select list field types store each option in the same table cell on a separate line. 

I’ve not worked with the Solspace Search module so I’m not familiar with it, but it doesn’t look like that’s the issue your resolving in your code.

thanks!
jules

@ ikiro you are just continually one post ahead of me!! LOL

do you use EE1 or EE2? Is the custom member field called “local_areas”?

EE2 and yes “local_areas” is my custom member field

jules

😉

Ok, if local_areas is field id 5 then try this:

<select name="local_areas">
<option value="0">Select One</option>    
{exp:query sql="SELECT m_field_id_5 FROM exp_members NATURAL JOIN exp_member_data ORDER BY m_field_id_5 ASC"}
<option value="{m_field_id_5}">{m_field_id_5}</option>
{/exp:query} 
</select>

Hi Jules,

If you get double results use “DISTINCT” in your query.

<select name="local_areas">
<option value="0">Select One</option>    
{exp:query sql="SELECT DISTINCT m_field_id_5 FROM exp_members NATURAL JOIN exp_member_data ORDER BY m_field_id_5 ASC"}
<option value="{m_field_id_5}">{m_field_id_5}</option>
{/exp:query} 
</select>

Hi ikiro

You were correct, I did need DISTINCT to prevent duplicate results.  I’m still having problems with two empty option tags.  Not sure what the problem is there.  I double check and there are not extra line entries in the database table cell.

Really appreciate your time and expertise!

<select name="local_areas">
  <option value="0">Select One</option>
  <option value=""></option>
  <option value=" "> </option>
  <option value="Bellingham">Bellingham</option>
  <option value="Bremerton">Bremerton</option>
  <option value="Ellensburg">Ellensburg</option>
  <option value="Olympia">Olympia</option>
  <option value="Other">Other</option>
  <option value="Seattle">Seattle</option>
  <option value="Sequim">Sequim</option>
  <option value="Snohomish Valley">Snohomish Valley</option>
  <option value="Spokane                       ">Spokane </option>
  <option value="Tri-Cities">Tri-Cities</option>
  <option value="Vancouver">Vancouver</option>
  <option value="Wenatchee—North Central WA">Wenatchee—North Central WA</option>
</select>

Hi ikiro

While researching what a NATURAL JOIN was and looking at the two tables that are involved I saw why I have two empty option tags.  In field m_field_id_5 two of my 25 test members have an an empty table cell.  The field was originally not set up as required and I guess I missed updating a few memberships.

Thank you so much for you help!!!

jules

Well, I’ve run into a bit of a snag.  Though the select list is populating correctly it doesn’t work within the exp:user:search tag.

When testing the form it doesn’t matter what area I choose I get the same results. The other search parameter fields are returning results correctly.

Any ideas on why this might be?

Hi Jules,

Good to hear your problem is solved.

Cheers,
Ralf

do you have a url where we can see the form