2

I've been trying to make a list of where our EE members comes from (country) and later on I will plot this data in to a Google Map to make it more interesting.

For the moment I've only come up with this solution, but it might not be the best solution to go through all the countries in the world.

<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Australia'"}Australia: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Denmark'"}Denmark: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Malaysia'"}Malaysia : {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'New Zealand'"}New Zealand: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Norway'"}Norway: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Philippines'"}Philippines : {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'Singapore'"}Singapore: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'United Kingdom'"}United Kingdom: {paginate}{total_pages}{/paginate}{/exp:query}</p>
<p>{exp:query limit="1" sql="SELECT m_field_id_3 FROM exp_member_data WHERE m_field_id_3 = 'United States'"}United States: {paginate}{total_pages}{/paginate}{/exp:query}</p>

I can't find any plug available that solve this. I'm not asking anyone to write the code for me, but I would appreciate if anyone could give me some tips/guidance.

Thanks in advance.

1

This query should do it:

<p>{exp:query sql="SELECT count(*) as member_count, m_field_id_3 as country FROM exp_member_data WHERE m_field_id_3 != '' GROUP BY m_field_id_3 ORDER BY m_field_id_3 ASC"}
    {country} ({member_count})<br />
{/exp:query}</p>
| 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.