Group check in SQL query

  • Resolved daveyuk

    (@daveyuk)


    3 years, 9 months ago

    I am trying to figure out a way of getting the group id in a mysql query.
    Here is the scinario:
    I have form data for different companies that i show on the front end in a table.
    Each company had a group name / id
    I am trying to write a query that i can integrate into another query to only show the form entries of the group that the site visitor is a member of.
    A member of the group ATLAS when viewing the page can only see form entries created by others in his group.
    Another member visits the page who is a member of the group PULSE and he will only see the form submissions from other PULSE members and nothing else.

    I dont really want to have to write an output table for every group.
    Here is the query i run to show the table data from the database:

    global $wpdb;
$result = $wpdb->get_results( "SELECT f_name, l_name, date, IF(score = max_score, 'PASSED','FAILED') AS score FROM wp_myplugin_data WHERE form_id=12");

    I am thinking something like IF currentuser_group = group_defined_in_usermeta, but havent got a clue how to get it to work.
    Any ideas how to write this into a mysql statement would be appreciated.
    Thanks

Viewing 1 replies (of 1 total)
  • Plugin Author Kento

    (@proaktion)

    Replying to this older topic because it can serve as a conceptual implementation example.

    Example: Your current user has ID 11. This query (using the standard prefix to make it simple to understand, see below *) retrieves the user IDs for those who are in the same groups as that user, except for the Registered group. Show form submissions from users with IDs in that group.

    SELECT user_id FROM wp_groups_user_group WHERE group_id IN (
SELECT group_id FROM wp_groups_user_group WHERE user_id = 11 AND group_id != 1);

    * Use Groups’ API instead, code is conceptual and untested:

    $user_id = get_current_user_id();
$groups_user = new Groups_User( $user_id );
$user_group_ids = $groups_user->group_ids;
$member_user_ids = array();
foreach ( $user_group_ids as $group_id ) {
    $group = new Groups_Group( $group_id );
    $member_user_ids = array_unique( array_merge( $member_user_ids, $group->user_ids ) );
}
$registered_group = Groups_Group::read_by_name(  Groups_Registered::REGISTERED_GROUP_NAME );
$member_user_ids = array_diff( $member_user_ids, array( $registered_group->group_id ) );

// $forms = get all the forms related to $member_user_ids
// show the forms

    You basically get the group IDs for the current user, excluding the ID of the Registered group because everyone belongs to that, then get all user IDs that belong to the same groups. Use those user IDs to retrieve appropriate forms and show them.

Viewing 1 replies (of 1 total)
  • The topic ‘Group check in SQL query’ is closed to new replies.