Community Support Forums

Re: Order by on member list

2011-12-13T23:38:50-05:00

Thanks for the kudos! Let us know if you need any help.

Statistics: Posted by Cristián Lávaque — December 13th, 2011, 11:38 pm

Re: Order by on member list

2011-12-11T06:40:12-05:00

Cheers guys, thanks for all the help, this forum has been so so useful!

I'll be doing another website using s2member ove the next couple of months, this one is much simpler though!

Thanks again

Zoe

Statistics: Posted by epixmedia — December 11th, 2011, 6:40 am

Re: Order by on member list

2011-12-11T02:23:18-05:00

Great job, Zoe! Glad you launched on time. The site looks nice.

Statistics: Posted by Cristián Lávaque — December 11th, 2011, 2:23 am

Re: Order by on member list

2011-12-10T17:00:08-05:00

Awesome, thanks for sharing that, Zoe, and congrats on the launch!

Statistics: Posted by Raam Dev — December 10th, 2011, 5:00 pm

Re: Order by on member list

2011-12-10T04:43:49-05:00

As its not possible to order by my custom company_name field, I've managed to sort by user_nicename by joining the wp_users table instead. Not ideal but an adequate compromise! Here's my code:

Code:: $users = $wpdb->get_results ("SELECT DISTINCT(U.user_id) as `ID` FROM " . $wpdb->usermeta . " AS U inner join " . $wpdb->usermeta . " as UMF on U.user_id = UMF.user_id inner join " . $wpdb->usermeta . " as UMS on U.user_id = UMS.user_id inner join " . $wpdb->usermeta . " as UML on U.user_id = UML.user_id inner join " . $wpdb->users . " as WU on U.user_id = WU.ID WHERE UMF.meta_key = '" . $wpdb->prefix . "s2member_custom_fields' AND UMF.meta_value REGEXP '.*\"".display."\";s:[0-9]+:\"".Yes."\".*' AND UML.meta_key = '" . $wpdb->prefix . "capabilities' AND UML.meta_value REGEXP '.*\"".s2member_level1."\";s:[0-9]+:\""."1"."\".*' ORDER BY WU.user_nicename LIMIT ".$start.", ".$limit."");

Thanks all -

Statistics: Posted by epixmedia — December 10th, 2011, 4:43 am

Re: Order by on member list

2011-12-10T04:31:38-05:00

Hi Raam, thank you so much for researching that for me - I really appreciate it!

So if I wanted to sort by first_name (which has its own meta key & value columns) - would that be possible?

My website went live yesterday, it still needs some tweaks but thanks to s2member its looking pretty awesome. http://www.newarkbusinessclub.co.uk/members-directory/

Cheers

Zoe

Statistics: Posted by epixmedia — December 10th, 2011, 4:31 am

Re: Order by on member list

2011-12-09T22:54:15-05:00

Hi Zoe,

I did some research on this and it appears that ordering by the company name is not possible. Here's why:

The company name is stored in the meta_key s2member_custom_fields, with a meta_value similar to this: a:1:{s:12:"company_name";s:4:"My Company";}.

So, to sort by company_name, we'd need to extract all the company names from the meta_value and then assign them to their own field. Once they're in their own field, we could use ORDER BY to sort the results by that field.

The problem lies in extracting the company names and assigning them to their own field. As you can see in your example above, you can use a REGEXP to filter your SQL query by specifying what the REGEXP should search for inside the meta_value.

However, to extract "My Company" from the meta_value, you'd need to use a REGEXP inside the MySQL LOCATE() function to find the start and end of the company name (e.g., "My Company") and then assign the result to its own field.

Unfortunately, the LOCATE() function does not allow for using a REGEXP inside, so we have no way of extracting "My Company" from meta_value and assigning it to its own field. That leaves us with no way to sort by company_name.

Does that make sense?

Statistics: Posted by Raam Dev — December 9th, 2011, 10:54 pm

Re: Order by on member list

2011-12-08T04:02:08-05:00

Would reaaaallllllyyyy appreciate some help on this, my client wants the site to go live today and its the last big issue on my list...

Thanks in advance!

Zoe

Statistics: Posted by epixmedia — December 8th, 2011, 4:02 am

Re: Order by on member list

2011-12-07T03:46:09-05:00

Hi Ramm, its one of the s2member_custom_fields meta values (company_name).

The main thing I've tried is adding another join and the trying to ORDER BY UMS.meta_value in various ways (including and not including the REGEX part...).

It would be wonderful if you could help me figure this out as its the last major change until it goes live!

Thanks

Zoe

Statistics: Posted by epixmedia — December 7th, 2011, 3:46 am

Re: Order by on member list

2011-12-06T23:21:01-05:00

Hi Zoe,

Can you tell me what you've tried so far (share the SQL examples if possible)?

You'll need to use the SQL ORDER BY keyword to sort by a specific field (it will go before the LIMIT keyword). So the question is, where in the database is the field you want to sort by?

Statistics: Posted by Raam Dev — December 6th, 2011, 11:21 pm

Order by on member list

2011-12-06T04:58:00-05:00

Hi all, this should be my last email before the website goes live! Thanks for all the support so far, this forum has been infinitely useful to me!

I've got my member listings and search working really well, I just need to order the members by the custom field company_name, I've tried several things and just can't get it to work! This is my code:

Code:: $users = $wpdb->get_results ("SELECT DISTINCT(U.user_id) as `ID` FROM " . $wpdb->usermeta . " AS U inner join " . $wpdb->usermeta . " as UMF on U.user_id = UMF.user_id inner join " . $wpdb->usermeta . " as UMS on U.user_id = UMS.user_id inner join " . $wpdb->usermeta . " as UML on U.user_id = UML.user_id WHERE UMF.meta_key = '" . $wpdb->prefix . "s2member_custom_fields' AND UMF.meta_value REGEXP '.*\"".display."\";s:[0-9]+:\"".Yes."\".*' AND UML.meta_key = '" . $wpdb->prefix . "capabilities' AND UML.meta_value REGEXP '.*\"".s2member_level1."\";s:[0-9]+:\""."1"."\".*' LIMIT ".$start.", ".$limit."");

Any suggestions are gratefully received!

Zoe

Statistics: Posted by epixmedia — December 6th, 2011, 4:58 am