Clearing Group 30 for Testing
⚠️ This will remove all users from group 30 except the 4 test members.
";
// Start transaction
$conn->begin_transaction();
try {
// Step 1: Find the 4 test users
echo "Step 1: Finding Test Users
";
$testUsers = [
'admin' => ['name' => 'Admin LadiesFriendlies', 'memberId' => '999930'],
'barry_kirk' => ['name' => 'KIRK, BARRY', 'memberId' => null],
'jo_musson' => ['name' => 'MUSSON, JOSEPHINE', 'memberId' => null],
'robert_hall' => ['name' => 'HALL, ROBERT', 'memberId' => null]
];
$foundUserIds = [];
// Find admin by memberId
$adminMemberId = "999930";
$findAdminStmt = $conn->prepare("SELECT id, name FROM Users WHERE memberId = ?");
$findAdminStmt->bind_param("s", $adminMemberId);
$findAdminStmt->execute();
$adminResult = $findAdminStmt->get_result();
if ($adminResult->num_rows > 0) {
$adminRow = $adminResult->fetch_assoc();
$foundUserIds['admin'] = $adminRow['id'];
echo "✅ Found admin: " . htmlspecialchars($adminRow['name']) . " (ID: " . $adminRow['id'] . ")
";
} else {
throw new Exception("Admin user with memberId 999930 not found.");
}
$findAdminStmt->close();
// Find other users by name (case-insensitive, handle variations)
$findUserStmt = $conn->prepare("SELECT id, name FROM Users WHERE UPPER(name) LIKE ?");
foreach (['barry_kirk', 'jo_musson', 'robert_hall'] as $key) {
$searchName = $testUsers[$key]['name'];
// Try exact match first, then with variations
$searchPattern = strtoupper($searchName) . '%';
$findUserStmt->bind_param("s", $searchPattern);
$findUserStmt->execute();
$result = $findUserStmt->get_result();
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
$foundUserIds[$key] = $row['id'];
echo "✅ Found " . htmlspecialchars($key) . ": " . htmlspecialchars($row['name']) . " (ID: " . $row['id'] . ")
";
} else {
// Try alternative search - remove comma variations
$altPattern = str_replace(',', '', strtoupper($searchName)) . '%';
$findUserStmt->bind_param("s", $altPattern);
$findUserStmt->execute();
$altResult = $findUserStmt->get_result();
if ($altResult->num_rows > 0) {
$row = $altResult->fetch_assoc();
$foundUserIds[$key] = $row['id'];
echo "✅ Found " . htmlspecialchars($key) . ": " . htmlspecialchars($row['name']) . " (ID: " . $row['id'] . ")
";
} else {
throw new Exception("User not found: " . htmlspecialchars($searchName));
}
}
}
$findUserStmt->close();
if (count($foundUserIds) !== 4) {
throw new Exception("Expected 4 users but found " . count($foundUserIds));
}
// Step 2: Check current users in group 30
echo "Step 2: Checking Current Users in Group 30
";
$checkUsersStmt = $conn->prepare("
SELECT igu.id, u.name, u.memberId
FROM InviteGroupUsers igu
JOIN Users u ON igu.id = u.id
WHERE igu.groupNo = 30
ORDER BY u.name
");
$checkUsersStmt->execute();
$result = $checkUsersStmt->get_result();
$currentUsers = [];
while ($row = $result->fetch_assoc()) {
$currentUsers[] = $row;
}
$checkUsersStmt->close();
$totalUsers = count($currentUsers);
echo "ℹ️ Found $totalUsers users currently in group 30
";
if ($totalUsers > 0) {
echo "";
echo "| ID | Name | Member ID | Status |
|---|
";
foreach ($currentUsers as $user) {
$isTestUser = in_array($user['id'], $foundUserIds);
$status = $isTestUser ? "✅ Keep" : "❌ Remove";
echo "";
echo "| " . $user['id'] . " | ";
echo "" . htmlspecialchars($user['name']) . " | ";
echo "" . htmlspecialchars($user['memberId']) . " | ";
echo "$status | ";
echo "
";
}
echo "
";
}
// Step 3: Delete all users except the 4 test users
echo "Step 3: Removing Users (Keeping Only Test Members)
";
$keepUserIds = array_values($foundUserIds);
$placeholders = implode(',', array_fill(0, count($keepUserIds), '?'));
$types = str_repeat('i', count($keepUserIds));
$deleteStmt = $conn->prepare("DELETE FROM InviteGroupUsers WHERE groupNo = 30 AND id NOT IN ($placeholders)");
// Bind parameters dynamically
$params = array_merge([$types], $keepUserIds);
$refs = [];
foreach ($params as $key => $value) {
$refs[$key] = &$params[$key];
}
call_user_func_array([$deleteStmt, 'bind_param'], $refs);
if ($deleteStmt->execute()) {
$deletedRows = $deleteStmt->affected_rows;
echo "✅ Successfully removed $deletedRows users from group 30
";
} else {
throw new Exception("Failed to delete users: " . $conn->error);
}
$deleteStmt->close();
// Step 4: Ensure all 4 test users are in the group
echo "Step 4: Ensuring Test Users Are in Group 30
";
foreach ($foundUserIds as $key => $userId) {
$checkStmt = $conn->prepare("SELECT COUNT(*) as count FROM InviteGroupUsers WHERE groupNo = 30 AND id = ?");
$checkStmt->bind_param("i", $userId);
$checkStmt->execute();
$checkResult = $checkStmt->get_result();
$checkRow = $checkResult->fetch_assoc();
$checkStmt->close();
if ($checkRow['count'] == 0) {
// Add user to group
$addStmt = $conn->prepare("INSERT INTO InviteGroupUsers (id, groupNo, createdAt, updatedAt) VALUES (?, 30, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)");
$addStmt->bind_param("i", $userId);
if ($addStmt->execute()) {
echo "ℹ️ Added " . htmlspecialchars($key) . " (ID: $userId) to group 30
";
} else {
throw new Exception("Failed to add user to group: " . $conn->error);
}
$addStmt->close();
} else {
echo "✅ " . htmlspecialchars($key) . " (ID: $userId) is already in group 30
";
}
}
// Commit transaction
$conn->commit();
echo "
";
echo "✅ All updates completed successfully!
";
echo "Summary:
";
echo "";
echo "- Removed $deletedRows users from group 30
";
echo "- Kept 4 test members in group 30:
";
echo "";
foreach ($foundUserIds as $key => $userId) {
$userNameStmt = $conn->prepare("SELECT name FROM Users WHERE id = ?");
$userNameStmt->bind_param("i", $userId);
$userNameStmt->execute();
$userNameResult = $userNameStmt->get_result();
$userNameRow = $userNameResult->fetch_assoc();
$userNameStmt->close();
echo "- " . htmlspecialchars($key) . ": " . htmlspecialchars($userNameRow['name']) . " (ID: $userId)
";
}
echo "
";
echo "
";
} catch (Exception $e) {
// Rollback transaction on error
$conn->rollback();
echo "❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
echo "Transaction rolled back. No changes were made.
";
}
// Show final state
echo "
";
echo "Final State Verification
";
// List all users in group 30
$finalUsersStmt = $conn->prepare("
SELECT u.id, u.name, u.email, u.memberId
FROM InviteGroupUsers igu
JOIN Users u ON igu.id = u.id
WHERE igu.groupNo = 30
ORDER BY u.name
");
$finalUsersStmt->execute();
$finalUsersResult = $finalUsersStmt->get_result();
$remainingCount = $finalUsersResult->num_rows;
echo "Total users in group 30: $remainingCount
";
if ($remainingCount > 0) {
echo "";
echo "| ID | Name | Email | Member ID |
|---|
";
while ($row = $finalUsersResult->fetch_assoc()) {
echo "";
echo "| " . $row['id'] . " | ";
echo "" . htmlspecialchars($row['name']) . " | ";
echo "" . htmlspecialchars($row['email']) . " | ";
echo "" . htmlspecialchars($row['memberId']) . " | ";
echo "
";
}
echo "
";
} else {
echo "⚠️ No users found in group 30
";
}
$finalUsersStmt->close();
$conn->close();
echo "