Order by on member list

[epixmedia]

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: Select all

               $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

[Raam Dev]

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?

[epixmedia]

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

[epixmedia]

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

[Raam Dev]

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?

[epixmedia]

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

[epixmedia]

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: Select all

$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 -

[Raam Dev]

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

[Cristián Lávaque]

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

[epixmedia]

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

[Cristián Lávaque]

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