Wordpress listing all members of a group on front-end

[vavroom]

I need to display the first name and last name of members of a smartgroup on the front-end of the site, to anyone (logged in or not).

I have read many things on the topic, including:

• http://book.civicrm.org/user/organising-your-data/profiles/
• https://wiki.civicrm.org/confluence/display/CRMDOC44/Linking+Profiles#LinkingProfiles-LinkingtoBuilt-inContactViewandEditPage%28s%29

I went to test on http://drupal.demo.civicrm.org/ but that site is currently b0rked. So tried on http://drupal.sandbox.civicrm.org/

I was able to get more or less what I want (more on that later in the post).

I tried the same thing on a local install of Civicrm on Wordpress. Did not work at all. So I tried it on http://wordpress.demo.civicrm.org and got a 404 when trying to load the page.

The page on Profile on the CiviCRM Book says "this feature is not available in WordPress". A post about a year old by Lobo indicates that this should actually be working as at 4.3 (http://forum.civicrm.org/index.php?topic=27575.0 ).

So, how can I make this happen on Wordpress?

Further, here are some things I need to modify to reach the display I want:

• Data returned via properly formed HTML, including header and id attribute for table cells as per http://www.w3.org/TR/WCAG20-TECHS/H43.html to increase accessibility. I realise that I am going to need to mod files for that. I'm just not sure which file(s) I need to change. I thought that creating a custom template might do the trick, as per http://wiki.civicrm.org/confluence/display/CRMDOC/Customize+Built-in,+Profile,+Contribution+and+Event+Registration+Screens but the .tpl file doesn't contain any html at all.
• Show an incremental count, starting at 1, of all displayed records. Again, probably need to hack something myself, but what?
• Only show the fields I created in the profile, not the contact type icon, the name, or the "view" link. This may be a question of profile setting, but playing with multiple variations on the settings hasn't returned that.
• Hide the message that says "Displaying contacts where: Contacts IN Summer Program Volunteers AND Group Status - 'Added'" and actually show the message that is in the pre-form help, or any other message I wish to display.

Any thoughts would be greatly appreciated.

Thank you

[Wes Reimer]

Read the post by Lobo carefully (the whole thread); I think most of the details are there, as well as the docs you've already read.

Among other things, you have to make sure anonymous users are allowed to view profile listings and you have to use a URL like http://yoursite.com/crm/?page=CiviCRM&q=civicrm/profile&force=1&gid=14 where gid is the ID of the profile. The "crm" is the Civi default page I set up on my WordPress installation, but I think the URL will work on almost any page as long as the query string is correct.

Having said that, in my own testing so far this is very buggy (especially with search) and hard to customize. I can't advise much on your other issues because I haven't tried those types of things (yet). Certainly you could hide some things via CSS (there is a Civi setting to indicate your CSS override file).

Also note that Civi caching for smart groups, as well as browser and server-side caching, may affect your results.

[vavroom]

I'm coming back to this. I was eventually able to make the listing work, but... Far from the result I want.

I *am* happy that the built in feature of listing 25, 50 or 100 records on the page, and navigating with prev/next is there. That's good. But...

If I include the First name and Last name field in my profile, it displays these two fields PLUS a name column, which basically repeats the other two and I have no control over that. Having no fields in the profile, I can't display anything, not even that name column.

It also shows a column with the contact type icon, as well as a column with a link to view more info about the record. Neither of these two columns are desired for the purpose.

Also, it is not playing nice with wordpress - As the link called isn't a page or post, the profile name gets loaded but not where titles normally go. Even when creating a WP page to use as the template so the profile doesn't load on the homepage. In addition to that, as I run a bilingual site, and you can only define one CIVICRM_UF_WP_BASEPAGE, regardless of which language I want to display, it loads on the language of that one basepage.

I'd really need to be able to do this via shortcode in a page, but the option to load a profile that way doens't let you display a list or configure it in anyway.

So I'm left thinking I need to write my own query to get the records I need, and php to include in a WordPress template to do what I need. I think I can cobble together the PHP to display the results (even if not elegantly), but I'm at a total loss about how to write this query to retrieve the records I want. I've looked at http://wiki.civicrm.org/confluence/display/CRMDOC/Useful+SQL+queries for inspiration, but it's not helping a whole lot Remember that I'm not a developer, nor do we have the funds to hire one

I would appreciate any pointers as to how to easily create a query that would return first and last name of the individuals in a specific smart group.

Thank you

Nic

[vavroom]

Well, I did it in the end. Not elegant, could likely be improved, but here's my solution if anyone is looking.

I created a WordPress page template. Basically copied my page.php from the WP theme directory. After the loop, I added some custom code.

Code: [Select]

    $mydb = new wpdb('user','password','database','server');

$rows = $mydb->get_results("SELECT civicrm_option_value.name AS 'Prefix', civicrm_contact.first_name AS 'FirstName', civicrm_contact.last_name AS 'LastName'
FROM (civicrm_group_contact_cache INNER JOIN civicrm_contact ON civicrm_group_contact_cache.contact_id = civicrm_contact.id) INNER JOIN civicrm_option_value ON civicrm_contact.prefix_id = civicrm_option_value.value
WHERE (((civicrm_group_contact_cache.group_id)=9) AND ((civicrm_option_value.option_group_id)=6))
ORDER BY civicrm_contact.last_name, civicrm_contact.first_name;");

$x = 1;
echo "<table>";
foreach ($rows as $obj) :
   echo "<tr>";
   echo "<td>". $x . "</td>";
   echo "<td>" . $obj->Prefix."</td>";
   echo "<td>". $obj->FirstName. "</td>";
   echo "<td>" . $obj->LastName. "</td>";
      echo "<td>MD</td>";
   echo "</tr>";
   $x = $x+1;
   
endforeach;
echo "</table>";
I limit the group to '9' which is the relevant identifier for the smartgroup I created that gives me the names I want.

I'm using the wpdb function of WordPress as it appears to be the best way to work it in that CMS. http://codex.wordpress.org/Class_Reference/wpdb

[vavroom]

Well... I had marked this as solved, but went back to the page a few hours later and, wouldn't you know it, I was getting no results whatsoever.

Probably was silly of me to rely on a smartgroup to do this, or at least to the cache table of the smartgroup. After looking at the smartgroup from the admin panel, and refreshing the public page, I got the right results.

Any ideas on how to make it work? Looks like I'm missing just a tiny elusive bit

Thx

[Chris Burgess]

If you need civicrm_group_contact_cache to be populated, you might be able to keep it populated by regularly hitting the URL where the profile page sits? That would rebuild the cache, and allow your SQL to get results. But that's a fairly dodgy quick fix which will break when the cache gets reset next.

Better would be to extract the query that populates the cache table and use that to get your results. That way you're actually getting the resultset when you need it. BUT! That will break when some future CiviCRM changes the SQL structure which the query refers to.

Best would be to use the API to get results, instead of SQL. You'd replace your SQL with an API query (https://github.com/civicrm/civicrm-core/tree/master/api/v3/examples/GroupContact). When CiviCRM's SQL structure changes, the API will be updated for compatibility and your code should still work on future CiviCRMs.

https://github.com/civicrm/civicrm-core/blob/master/api/v3/examples/GroupContact/GetWithGroupID.php - get contacts belonging to group

Aside from the syntax (documented in first function of file above) API should work just like querying SQL.

One common gotcha is that SQL has no LIMIT if not specified, while CiviCRM limits to 25 results by default (use $params['options']['limit'] and $params['options']['offset'] to customise this, other options in api/v3/utils.php). Depending on your use LIMIT 0 may not be suitable (fetching 100,000 contact civmcrm_contact results from SQL and loading 100,000 contacts at once through CiviCRM are different propositions).

[vavroom]

Thanks Chris, I much appreciate your time.

I fear I'm thick though. I spent several hours reading the API docs, looking at the examples, trying the API explorer and generator, and I'm not getting any joy.

1) I am not managing to get any results of an API call to display on my front end. I tried some of the examples as is, even the simple get ones. Not working for me. I put the code in my WP page template, just like I'd done with the query example I used in a previous post. But nothing I do seems to output results on the page. I am *obviously* missing something, but it's one of those "you don't know that you don't know", hence I don't even know where to start looking.

2) Looking at the various examples, I'm not sure what the API call would be to return the fields I want only for the people in the specific smartgroup I need.

As for limiting to 25, I am happy to change the limit, or keep it. Currently the smartgroup has about 600 people, it'll be growing. So limiting to, say a 100 per page, is not a bad idea. Just as long as I manage to make it work!

[vavroom]

Right, so I've been playing further with this, getting a bit further ahead. At least I managed to get *some* response out of the API on my WordPress page!

I've read and re-read the API docs and stared till I'm crosseyed. I'm not finding the system generated examples particularly clear (to my non-coder's eye anyway).

So, I came up with the following bit of code:

Code: [Select]

$contact = civicrm_api('Contact','Get',array('group_id' => '9', 'contact_type' => 'Individual', 'version' =>3, 'options' => array ('limit' =>'100')));

 foreach ($contact['values'] as $out) {
$contact[$out['id']]=$id;
echo "<p>". $contact['values'][$id]['first_name']. " " . $contact['values'][$id]['last_name']."</p>";
 }

It doesn't work. Doh! I believe the nested arrays are what is toying with me. If I put $contact['values'][hard-coded-id]['first_name], I get the correct first name, for that ID. But I need it to cycle through each ID in the array.

What am I missing? What do I need to do to get it to go through?

Thanks

Nic

[Chris Burgess]

Try this form? If you give two parameters to foreach (key, value) then you don't need to retrieve the key separately.

Code: [Select]

$contacts = civicrm_api('Contact','Get',array('group_id' => '9', 'contact_type' => 'Individual', 'version' =>3, 'options' => array ('limit' =>'100')));
foreach ($contacts['values'] as $id => $contact) {
  echo '<p>' . $contact['first_name'] . '</p>';
}

If you get stuck,

Code: [Select]

print '<pre>' . print_r($contact,1) . '</pre>';

(substitute whichever value you're trying to make sense of)

[Chris Burgess]

NB. Code untested - I need to get around to making a Wordpress CiviCRM set up to play with locally so I can be more helpful with Wordpress questions! API code should be the same but just a quick reply while I wait for an upload to finish

[vavroom]

Chris you're a legend! That's exactly what I needed. Thank you.

However, now that the output looks the way I want, I realise it's not the dataset I actually want

I, perhaps foolishly, assumed that the Contact API having 'group_id' as parameter, I could use it to filter the contacts to only those in a specific group.

Turns out that it's 'group' parameter that I should have been using. Because now that I'm using *that*, it works.

Thanks to all who helped along the way.