Create a user corresponding to each record in a table

In this article, we’ll show how to automatically create a user account for each record in a table. This is useful when you have a table of users and you want to create a corresponding user account for each user in the membership_users table.

For example, let’s say you have an application for managing rental units, and you have a table named tenants that contains the details of each tenant. You want to create a user account for each tenant in the membership_users table. This way, each tenant can log in to the application and view their details, rent payments, submit maintenance requests, etc.

Another example, which we’ll be using below, is a customers table. We’ll show how to create a user account for each customer. This would allow each customer to log in to the application and view their orders, invoices, etc.

User tables

In order to be able to create a user account for each record in a table, you need to have a table in your AppGini application that contains the details of each user. This table should contain at least an email field, which will be used as the username for the user account. It’s also recommended to set this email field as unique in the table structure.

You also need to create a user group in the admin area that corresponds to the table. For example, if you have a customers table, you should create a user group named Customers, with the appropriate permissions. Those permissions should allow users in this group to typically view their own records in specific tables, and possibly edit them.

Adding a new record to the users table

The screenshot below shows the message displayed after adding a new customer to the customers table. The message indicates that a user account has been created for the customer. It includes the username, which is the same as the customer email, an initial strong password, and the user group to which the user account was added.

Showing the status of an existing user

When you open an existing customer record in the customers table, the status message at the top of the page shows that a user account already exists for this customer. It includes the username, which is the same as the customer email, and a link to view the user details in the admin area.

Clicking the View button from the status message opens the user details in admin area:

Part of the user details visible in the admin area is the comment field, which indicates the customer ID corresponding to this user account, as well as the initial password that was set when the user account was created.

Code snippets for automatic user creation

tablename_dv code

In the generated hooks/tablename.php file, add the following code line inside the tablename_dv() function, where tablename is the name of the table for which you want to create user accounts.

1

if($selectedID) $html .= createUserIfNotExists('tablename', $selectedID);

Replace tablename with the actual name of the table, for example, customers or tenants. The above line checks if an existing table record is being viewed in the detail view (dv) page. If so, it calls the createUserIfNotExists() function to create a user account for this record if it doesn’t already exist. The function also displays a notification message indicating the status of the user account creation.

Tip: If you have multiple types of users in your application, you can add the above line for each user table in the tablename_dv() function. For example, if you want to create users for both customers and employees tables, you can follow the above step for both tables. And you need to have a Customers user group and an Employees user group pre-defined in the admin area.

The createUserIfNotExists() function in hooks/__bootstrap.php

Add the following function to the hooks/__bootstrap.php file. If the file doesn’t exist, create it in the hooks folder.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<?php 

function createUserIfNotExists($tablename, $id) {
	$data = getRecord($tablename, $id);

	// if email is not empty and is valid, check if there is a matching user account (`membership_users` table).
	// if no matching user account is found, create a new user account.
	if(!isEmail($data['email'] ?? ''))
		return Notification::show([
			'message' => 'Could not check or create username for this user. Reason: invalid email address: ' . $data['email'],
			'class' => 'danger',
			'dismiss_seconds' => 3600,
		]);

	$email = $data['email'];

	// check if there is a matching user account
	$username = sqlValue("SELECT `memberID` FROM `membership_users` WHERE `email`='$email' OR `memberID`='$email'");
	if($username)
		return Notification::show([
			'message' => 'User account already exists for the email ' . $email . '. Username: ' . $username .
			            (getLoggedAdmin() ? ' <a target="_blank" href="admin/pageEditMember.php?memberID=' . urlencode($email) . '" class="btn btn-default alert-btn">View</a>' : ''),
			'class' => 'info',
			'dismiss_seconds' => 3600,
		]);

	// no matching user account found, create a new user account
	$username = $email;
	$groupID = sqlValue("SELECT `groupID` FROM `membership_groups` WHERE `name` LIKE '$tablename'");
	if(!$groupID)
		return Notification::show([
			'message' => "Couldn't create user account with the email $email because the group '$tablename' doesn't exist. You need to create this group in the admin area first.",
			'class' => 'danger',
			'dismiss_seconds' => 3600,
		]);
	
	$password = substr(md5($email . microtime()), 0, 12); // generate a random initial password
	insert('membership_users', [
		'memberID' => $email,
		'passMD5' => password_hash($password, PASSWORD_DEFAULT),
		'email' => $email,
		'signupDate' => date('Y-m-d'),
		'groupID' => $groupID,
		'isBanned' => 0,
		'isApproved' => 1,
		'comments' => "Automatically created from user ID $id from table $tablename. Initial password: $password",
	]);

	return Notification::show([
		'message' => 'User account created for the email ' . $email . '. Initial password: ' . $password . '<br>Group: $tablename',
		'class' => 'success',
		'dismiss_seconds' => 3600,
	]);
}

Let’s go through the above function step by step (you might skip this if you’re not interested in the details):

1. The function createUserIfNotExists() takes two arguments: $tablename and $id. $tablename is the name of the table for which you want to create user accounts, and $id is the ID of the record in this table.
2. The function first fetches the record data from the table using the getRecord() function.
3. It then checks if the email field in the record is not empty and is a valid email address. If not, it displays an error message and returns.
4. It then checks if there is a matching user account in the membership_users table for this email address. If a matching user account is found, it displays a message indicating that the user account already exists and returns.
5. If no matching user account is found, the function generates a random initial password and inserts a new record in the membership_users table. The new record includes the email address as the username, the generated password, the user group ID corresponding to the table, and some other fields.
6. Finally, the function displays a success message indicating that the user account has been created and returns.

Troubleshooting, when things don’t work as expected

If you encounter any issues while implementing the above steps, here are some tips to help you troubleshoot:

1. Check the email field in the table: Make sure the table for which you want to create user accounts has an email field that contains valid email addresses. The email field should be set as unique in the table structure.
2. Check the user group in the admin area: Make sure you have created a user group in the admin area that corresponds to the table. The user group should have the appropriate permissions to view and edit records in the table.
3. Check the tablename_dv() function: Make sure you have added the line to call the createUserIfNotExists() function in the tablename_dv() function in the hooks/tablename.php file, where tablename is the name of the table.

Conclusion

In this article, we showed how to automatically create a user account for each record in a table. This can be useful when you have a table of users and you want to create corresponding user accounts for each user in the membership_users table. By following the steps outlined in this article, you can automate the process of creating user accounts for users in your application, making it easier for them to log in and access their data.