Assigning Specific Sub-Level Categories To All Members Via An SQL Command

Link: https://support.brilliantdirectories.com/support/solutions/articles/12000002543

This article demonstrates how to assign a specific sub-level category to every member in the database using a SQL query. This approach is useful when multiple category trees contain sub-level categories with identical names.

Why This Happens

When a website contains multiple parent categories with sub-level categories that share the same name, the member import process cannot determine which category tree should be used.

The example below shows two parent categories that both contain the same sub-level category names.

Because the import file only stores the sub-level category name, Brilliant Directories matches the first occurrence it finds in the database. This can result in members being assigned to the wrong category.

Import Members Without Services

When importing members into a category structure like the one above, leave the services column empty.

The example below illustrates an import file containing duplicate sub-level category names. In this scenario, including the services field may assign members to the incorrect category tree.

After the import is complete, run the SQL query below to assign the desired sub-level category to all imported members.

Important

This query assigns the specified sub-level category to every member in the database. Before running it, create a database backup and confirm that the selected service_id is correct.

SQL Query

The example below assigns the sub-level category with a service_id of 135 to every member.

INSERT INTO rel_services (user_id, service_id)
SELECT user_id, 135
FROM users_data;

Update the Service ID

Replace 135 with the service_id of the desired sub-level category.

The corresponding IDs can be found in the list_services database table.

Summary

When multiple parent categories contain sub-level categories with the same name, the import process cannot distinguish between them.

Importing members without the services field and assigning the correct service_id afterward ensures every member is associated with the intended sub-level category.