# Fix 500 Error - Complete Guide

## Problem Identified

Your application is returning a 500 error because:

1. **Missing Database Column**: The `account` table is missing the `username` column
2. **Code Mismatch**: `user_access.php` queries for `username` field that doesn't exist
3. **SQL Injection**: Unescaped variables in database queries
4. **Poor Error Handling**: Using `die()` which causes 500 errors instead of graceful handling

## Solution - Step by Step

### Step 1: Backup Your Database

```bash
# Run this command to backup your database first
mysqldump -u alliedcreditunio_mkdb -p alliedcreditunio_mkdb > backup_before_fix.sql
```

### Step 2: Run the Database Fix Script

**Option A: Using phpMyAdmin**
1. Open phpMyAdmin
2. Select database: `alliedcreditunio_mkdb`
3. Click "SQL" tab
4. Copy and paste the contents of `fix_database.sql`
5. Click "Go" to execute

**Option B: Using MySQL Command Line**
```bash
mysql -u alliedcreditunio_mkdb -p alliedcreditunio_mkdb < fix_database.sql
```

### Step 3: Verify Database Changes

Run this query in phpMyAdmin to verify:

```sql
SHOW COLUMNS FROM account LIKE 'username';
```

You should see the `username` column listed.

Check your data:

```sql
SELECT id, acc_no, uname, username, fname, lname FROM account LIMIT 5;
```

Expected result:
- `uname` = account number (e.g., '9180074482')
- `username` = login username (e.g., 'Webster')

### Step 4: Test the Login

1. Go to your login page
2. Try logging in with:
   - **Username**: Webster
   - **Password**: 12345678!

### Step 5: Update Existing Accounts (if needed)

If you have other accounts that need usernames, run:

```sql
-- This will set username = account number for all accounts
UPDATE account 
SET username = uname 
WHERE username IS NULL OR username = '';
```

Or use the `populate_usernames.php` script to generate custom usernames.

## What Was Fixed

### 1. Database Schema (`alliedcreditunio_xtdb.sql`)
- Added `username` column to the CREATE TABLE statement
- Updated INSERT statement to include username value

### 2. Login File (`user_access.php`)
- Added `mysqli_real_escape_string()` to prevent SQL injection
- Added proper error handling instead of `die()`
- Fixed PDO prepared statements with parameter binding
- Added error logging for debugging

### 3. Created Migration Script (`fix_database.sql`)
- Adds username column if missing
- Populates usernames from existing data
- Adds database index for performance

## Understanding the Fields

| Field | Purpose | Example |
|-------|---------|---------|
| `acc_no` | Account number (display) | '9180074482' |
| `uname` | Account number (internal) | '9180074482' |
| `username` | Login username | 'Webster' |
| `upass` | Password | '12345678!' |

## Common Issues & Solutions

### Issue: "Column 'username' doesn't exist"
**Solution**: Run `fix_database.sql` script

### Issue: "Invalid Username or Password"
**Solution**: Check that username field is populated:
```sql
SELECT username FROM account WHERE id = 517;
```

### Issue: Still getting 500 error
**Solution**: Check PHP error logs:
```bash
# Linux
tail -f /var/log/apache2/error.log

# Windows with XAMPP
# Check: C:\xampp\apache\logs\error.log
```

### Issue: Duplicate usernames
**Solution**: Make usernames unique:
```sql
-- Find duplicates
SELECT username, COUNT(*) as count 
FROM account 
GROUP BY username 
HAVING count > 1;

-- Fix by adding numbers
UPDATE account SET username = CONCAT(username, '_', id) 
WHERE username IN (SELECT username FROM (SELECT username FROM account GROUP BY username HAVING COUNT(*) > 1) as dups);
```

## Security Improvements Made

1. **SQL Injection Prevention**: Added `mysqli_real_escape_string()` and PDO parameter binding
2. **Error Handling**: Replaced `die()` with proper error messages
3. **Error Logging**: Added `error_log()` for debugging without exposing errors to users

## Next Steps (Recommended)

1. **Make username required**:
```sql
ALTER TABLE account MODIFY COLUMN username VARCHAR(100) NOT NULL;
```

2. **Add unique constraint**:
```sql
ALTER TABLE account ADD UNIQUE KEY unique_username (username);
```

3. **Hash passwords** (currently stored in plain text):
```php
// When creating/updating passwords
$hashed_password = password_hash($upass, PASSWORD_DEFAULT);

// When verifying login
if (password_verify($upass, $row['upass'])) {
    // Login successful
}
```

4. **Delete test files** after verification:
- `test_username_login.php`
- `populate_usernames.php`
- `FIX_500_ERROR_GUIDE.md` (this file)

## Testing Checklist

- [ ] Database backup created
- [ ] `fix_database.sql` executed successfully
- [ ] `username` column exists in account table
- [ ] Test account (Webster) has username populated
- [ ] Login page loads without 500 error
- [ ] Can login with username and password
- [ ] OTP/PIN authentication works after login
- [ ] Test files deleted after successful testing

## Support

If you still encounter issues:

1. Check PHP error logs for specific error messages
2. Verify database connection in `include/dbconfig.php`
3. Ensure all files have proper permissions
4. Check that PHP PDO extension is enabled

## Files Modified

1. `alliedcreditunio_xtdb.sql` - Added username column
2. `user_access.php` - Fixed SQL injection and error handling
3. `fix_database.sql` - NEW: Database migration script
4. `FIX_500_ERROR_GUIDE.md` - NEW: This guide
