BuddyPress

I have been trying this for a couple of days now and I haven’t quite figured it out. Am pretty good with wordpress and the $bp globals but this one has me stumped. Basically in my group extension pugin I have added the meta_key’s state and country with their corresponding values. I am tying to get the groups that have the meta_value of a certain country. in my phpmyadmin sql it works great

SELECT g.group_id, g.meta_value FROMwp_bp_groups_groupmetaas g inner joinwp_bp_groups_groupmetaas gm2 on g.group_id = gm2.group_id WHERE gm2.meta_value = 'United States' AND g.meta_key = 'state' GROUP BY g.meta_value

the results is 53 rows

but when I use it in my function, which has been globalized with the $wpdb and $bp globals and the query being

$result = $wpdb->get_results( "SELECT g.group_id, g.meta_value FROM FROM {$bp->groups->table_name_groupmeta} as g inner join {$bp->groups->table_name_groupmeta} as gm2 on g.group_id = gm2.group_id WHERE gm2.meta_value = 'United States' AND g.meta_key = 'state' GROUP BY g.meta_value ",ARRAY_A );

var_dump($result);

I get an empty array. I am and I have tried using correlated subqueries and now using a join. Any help will be apreciated

MIght sound like a no-brainer, but have you declared the globals?
global $wpdb, $bp;

You also have FROM twice in your query.

Also, I don’t see why you’d need the inner join. All you need to do is this:
$result = $wpdb->get_results( $wpdb->prepare( "SELECT group_id, meta_value FROM {$bp->groups->table_name_groupmeta} WHERE meta_value = 'United States' AND meta_key = 'state' GROUP BY meta_value" ) );

Haven’t actually tried the above query, but should be alright.

Yes travel Junkie, I have declared the
global $wpdb, $bp ;

lol@ the no brainer

WOW just tried it out and works like a charm. I should know better with typos and stuff. I was condensing my FROM statements from a larger query to something more efficient and totally missed the extra FROM. …TIRED I need a nap

Thank you