# KSO Chandigarh - Auto-Increment KSO ID System

## Overview
The system now automatically generates KSO IDs in format: KSOCHD001, KSOCHD002, KSOCHD003, etc.

## Database Changes
- Added `kso_id_sequence` table for unique number generation
- Updated triggers for automatic KSO ID generation
- Members and dependents get unique sequential IDs

## Implementation
- Database triggers handle ID generation automatically
- PHP code no longer manually generates KSO IDs
- Unique constraints prevent duplicate IDs

## Benefits
- Automatic sequential numbering
- No manual intervention required
- Thread-safe for concurrent registrations
- Consistent ID format across all records

## Database Implementation

### 1. Sequence Table
A dedicated sequence table is used to generate unique numbers:

```sql
CREATE TABLE `kso_id_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
```

### 2. Database Triggers

#### For Members Table:
```sql
CREATE TRIGGER `generate_kso_id` BEFORE INSERT ON `members`
FOR EACH ROW
BEGIN
    IF NEW.kso_id IS NULL OR NEW.kso_id = '' THEN
        INSERT INTO kso_id_sequence () VALUES ();
        SET NEW.kso_id = CONCAT('KSOCHD', LPAD(LAST_INSERT_ID(), 3, '0'));
    END IF;
END
```

#### For Dependents Table:
```sql
CREATE TRIGGER `generate_dependent_kso_id` BEFORE INSERT ON `dependents`
FOR EACH ROW
BEGIN
    IF NEW.kso_id IS NULL OR NEW.kso_id = '' THEN
        INSERT INTO kso_id_sequence () VALUES ();
        SET NEW.kso_id = CONCAT('KSOCHD', LPAD(LAST_INSERT_ID(), 3, '0'));
    END IF;
END
```

## How It Works

1. **Automatic Generation**: When a new member or dependent is inserted without specifying a `kso_id`, the trigger automatically generates one.

2. **Unique Sequence**: Each insertion into the `kso_id_sequence` table generates a unique auto-increment number.

3. **Format**: The system concatenates "KSOCHD" with a zero-padded 3-digit number (e.g., "001", "002", "003").

4. **Uniqueness**: The `kso_id` field has a UNIQUE constraint to prevent duplicates.

## PHP Implementation

### Signup Process

The `signup.php` file has been updated to work with the auto-increment system:

```php
// Insert member without specifying kso_id - let the trigger handle it
$query = mysqli_query($con, "INSERT INTO members (first_name, last_name, email, phone, date_of_birth, gender, blood_group, college, course, year_of_study, address, emergency_contact_name, emergency_contact, membership_type, status) VALUES ('$fname', '$lname', '$email', '$phone', '$dob', '$gender', '$blood_group', '$college', '$course', '$year_of_study', '$address', '$emergency_contact_name', '$emergency_contact', '$membership_type', 'active')");

if ($query) {
    $member_id = mysqli_insert_id($con);
    
    // Get the generated KSO ID
    $kso_query = mysqli_query($con, "SELECT kso_id FROM members WHERE id = '$member_id'");
    $kso_result = mysqli_fetch_array($kso_query);
    $kso_id = $kso_result['kso_id'];
    
    echo "<script>alert('Member registration successful! Welcome to KSO Chandigarh. Member ID: $kso_id');</script>";
}
```

### Family Membership Dependents

For family memberships, dependents also get unique KSO IDs:

```php
// Insert dependent without specifying kso_id - let the trigger handle it
$dep_insert = mysqli_query($con, "INSERT INTO dependents (member_id, first_name, last_name, relationship, date_of_birth, gender, course, college, year_of_study) VALUES ('$member_id', '$dep_fname', '$dep_lname', '$dep_relationship', '$dep_dob', '$dep_gender', '$dep_course', '$dep_college', '$dep_year')");
```

## Example Output

When members register, they will receive KSO IDs like:
- First member: `KSOCHD001`
- Second member: `KSOCHD002`
- Third member: `KSOCHD003`
- First dependent: `KSOCHD004`
- Second dependent: `KSOCHD005`

## Database Schema Updates

### Members Table
- `id` field is now AUTO_INCREMENT
- `kso_id` field has UNIQUE constraint
- `email` field has UNIQUE constraint

### Dependents Table
- `id` field is now AUTO_INCREMENT
- `kso_id` field has UNIQUE constraint
- `member_id` field has INDEX for performance

## Testing

To test the system:

1. Import the updated SQL file (`ktxmmosu_kso_chandigarh.sql`)
2. Register a new member through the signup form
3. Check that a KSO ID is automatically generated
4. Register another member to verify sequential numbering
5. Test family membership with dependents

## Maintenance

- The `kso_id_sequence` table will grow over time but can be cleaned up periodically
- The triggers ensure data integrity
- No manual intervention required for KSO ID generation

## Error Handling

- If a KSO ID is manually specified during insertion, the trigger will not override it
- Duplicate KSO IDs are prevented by the UNIQUE constraint
- Database errors are handled gracefully in the PHP code

This system ensures that every KSO Chandigarh member and dependent receives a unique, sequential identifier automatically upon registration. 