# Database Integration Guide - Services Page

## ✅ Overview

The `services.php` page is now fully connected to the database and uses dynamic content. Services are pulled from the `services` table in the `powerzone_db` database, allowing real-time updates without editing the PHP code.

---

## 📊 Database Schema

### Services Table Structure

```sql
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    duration VARCHAR(50),
    icon VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
```

### Field Descriptions

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `id` | INT | Unique service ID | 1 |
| `name` | VARCHAR(255) | Service name | "Gym Training" |
| `description` | LONGTEXT | Detailed service description | "Access our state-of-the-art fitness..." |
| `price` | DECIMAL(10,2) | Service price | 49.00 |
| `duration` | VARCHAR(50) | Duration period | "1 month" or "1 session" |
| `icon` | VARCHAR(255) | Emoji or icon representation | "🏋️" |
| `status` | ENUM | Service status | 'active' or 'inactive' |
| `created_at` | TIMESTAMP | Service creation date | 2024-01-19 10:30:45 |
| `updated_at` | TIMESTAMP | Last update date | 2024-01-19 10:30:45 |

---

## 🔄 How It Works

### 1. Database Connection
```php
<?php
session_start(); 
include "includes/db.php";  // Connects to powerzone_db
include "header.html";
```

### 2. Fetch Services
```php
// Fetch all active services from database
$services_query = "SELECT * FROM services WHERE status = 'active' ORDER BY id ASC";
$services_result = mysqli_query($conn, $services_query);
$services = [];

if ($services_result) {
    while ($row = mysqli_fetch_assoc($services_result)) {
        $services[] = $row;
    }
}
```

### 3. Auto-Populate Sample Data
If no services exist in the database, the page automatically inserts 6 sample services:
- Gym Training ($49/month)
- Personal Training ($60/session)
- Strength Training ($55/session)
- Weight Training ($50/session)
- Functional Training ($45/session)
- CrossFit Training ($65/month)

```php
// If no services exist, insert sample services
if (empty($services)) {
    $sample_services = [
        // 6 pre-configured services
    ];
    
    // Insert each service
    foreach ($sample_services as $service) {
        $stmt = $conn->prepare("INSERT INTO services (name, description, price, duration, icon, status) 
                               VALUES (?, ?, ?, ?, ?, 'active')");
        $stmt->bind_param("ssdss", ...);
        $stmt->execute();
    }
}
```

### 4. Display Dynamic Services
```php
<?php foreach ($services as $service): ?>
    <div class="service-card">
        <div class="service-icon"><?php echo htmlspecialchars($service['icon']); ?></div>
        <h3 class="service-title"><?php echo htmlspecialchars($service['name']); ?></h3>
        <p class="service-description"><?php echo htmlspecialchars($service['description']); ?></p>
        <!-- Features, Price, Button -->
        <div class="service-price">
            <span class="currency">$</span>
            <?php echo htmlspecialchars($service['price']); ?>
            <span>per <?php echo htmlspecialchars($service['duration']); ?></span>
        </div>
    </div>
<?php endforeach; ?>
```

---

## 🎯 Features

✅ **Dynamic Content**: Services displayed from database, not hardcoded
✅ **Auto-Populate**: Sample data inserted on first load if table is empty
✅ **Real-time Updates**: Changes in database instantly reflect on the page
✅ **Easy Management**: Add/edit/delete services via database or admin panel
✅ **Responsive Design**: Mobile-friendly grid layout
✅ **XSS Protection**: All output sanitized with `htmlspecialchars()`
✅ **Status Control**: Inactive services hidden from public display

---

## 📝 Managing Services

### Add a New Service

#### Method 1: Using PHP Admin Tool (Recommended)
1. Login to `admin-login.php`
2. Navigate to Services Management
3. Click "Add New Service"
4. Fill in: Name, Description, Price, Duration, Icon
5. Click Save

#### Method 2: Direct Database Insert
```sql
INSERT INTO services (name, description, price, duration, icon, status) 
VALUES (
    'Yoga Training',
    'Flow-based yoga sessions combining strength, flexibility, and mindfulness.',
    40.00,
    '1 session',
    '🧘',
    'active'
);
```

### Update a Service
```sql
UPDATE services 
SET description = 'New description here',
    price = 55.00,
    updated_at = CURRENT_TIMESTAMP
WHERE name = 'Personal Training';
```

### Deactivate a Service
```sql
UPDATE services 
SET status = 'inactive',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 2;
```

### Delete a Service
```sql
DELETE FROM services WHERE id = 1;
```

---

## 🔗 Integration with Other Pages

### Similar Pattern in Team Members (`team.php`)
- Queries: `SELECT * FROM team_members WHERE status = 'active'`
- Displays trainer profiles dynamically
- Same database connection method

### Similar Pattern in Blog (`blog-grid-4-masonary.php`)
- Queries: `SELECT * FROM blog_posts WHERE status = 'published'`
- Displays blog articles dynamically
- Uses pagination for multiple posts

### Your Services Page (`services.php`)
- Queries: `SELECT * FROM services WHERE status = 'active'`
- Displays fitness training services dynamically
- Auto-populates on first run

---

## 🔒 Security

### Data Sanitization
```php
// Safe output - prevents XSS attacks
<?php echo htmlspecialchars($service['name']); ?>
```

### Prepared Statements
```php
// Safe database inserts - prevents SQL injection
$stmt = $conn->prepare("INSERT INTO services (name, description, price, duration, icon, status) VALUES (?, ?, ?, ?, ?, 'active')");
$stmt->bind_param("ssdss", $name, $description, $price, $duration, $icon);
```

### Status Control
```php
// Only show active services
$services_query = "SELECT * FROM services WHERE status = 'active' ORDER BY id ASC";
```

---

## 🚀 Testing the Integration

### Step 1: Initialize Database
Visit `http://localhost/fitness/setup-database.php`
- Creates services table if not exists
- No sample data needed (page auto-populates)

### Step 2: View Services Page
Visit `http://localhost/fitness/services.php`
- First load auto-inserts 6 sample services
- Displays all active services in responsive grid

### Step 3: Test Dynamic Updates
```sql
-- Add a new service
INSERT INTO services (name, description, price, duration, icon, status) 
VALUES ('Pilates Training', 'Core strengthening...', 42.00, '1 session', '🤸', 'active');

-- Visit services.php again - new service appears immediately!
```

---

## 🛠️ Troubleshooting

### Services Not Showing
**Problem**: Services page appears empty
**Solution**: 
1. Run `setup-database.php` to create the table
2. Refresh `services.php` to auto-populate sample data
3. Check MySQL is running via XAMPP control panel

### Duplicate Services
**Problem**: Services appear multiple times
**Solution**: 
```sql
-- Delete duplicates and reset
DELETE FROM services WHERE id > 6;
```

### Database Connection Error
**Problem**: "db_error" message or connection failure
**Solution**: Verify in `includes/db.php`:
```php
$conn = mysqli_connect("localhost", "root", "", "powerzone_db");
if (!$conn) {
    die("db_error");
}
```

### Changes Not Reflecting
**Problem**: Updated service prices still show old values
**Solution**: 
1. Clear browser cache (Ctrl+F5)
2. Verify changes were saved to database
3. Check service status is 'active'

---

## 📋 Database Query Reference

### Get All Active Services
```sql
SELECT * FROM services WHERE status = 'active' ORDER BY id ASC;
```

### Get Service by Name
```sql
SELECT * FROM services WHERE name = 'Personal Training';
```

### Get Services by Price Range
```sql
SELECT * FROM services WHERE price BETWEEN 40 AND 60 ORDER BY price ASC;
```

### Count Total Services
```sql
SELECT COUNT(*) as total FROM services WHERE status = 'active';
```

### Get Service with Highest Price
```sql
SELECT * FROM services WHERE status = 'active' ORDER BY price DESC LIMIT 1;
```

---

## 🔄 Syncing with Admin Dashboard

Services can also be managed through the admin dashboard:
- **URL**: `http://localhost/fitness/admin-dashboard.php`
- **Username**: admin
- **Password**: admin123

In Admin Dashboard → Services Section:
- View all services in a table
- Edit service details
- Change status (active/inactive)
- Delete services

All changes sync automatically across the website.

---

## 📞 Support & Next Steps

### Now That Services Are Dynamic:

✅ **Easy Updates**: Change prices, descriptions in database
✅ **Add New Services**: Use admin panel or SQL insert
✅ **Manage Status**: Activate/deactivate without code changes
✅ **Scale Content**: Add unlimited services, they'll all display

### Recommended Next Steps:

1. **Connect Appointments**: Link appointments to services in database
2. **Add Admin Features**: Create service management interface
3. **Advanced Queries**: Filter by price, duration, popularity
4. **Content Caching**: Speed up for high-traffic sites
5. **API Integration**: Expose services via REST API

---

## 📊 Sample Data

When you first visit `services.php`, these 6 services are auto-populated:

| Name | Price | Duration | Description |
|------|-------|----------|-------------|
| Gym Training | $49 | 1 month | State-of-the-art facility with cardio and free weights |
| Personal Training | $60 | 1 session | One-on-one certified professional coaching |
| Strength Training | $55 | 1 session | Build power and muscle mass |
| Weight Training | $50 | 1 session | Master barbell and dumbbell techniques |
| Functional Training | $45 | 1 session | Real-world fitness and movement patterns |
| CrossFit Training | $65 | 1 month | High-intensity group training classes |

---

**Last Updated**: January 19, 2026  
**Status**: ✅ Complete & Ready  
**Database**: powerzone_db  
**Version**: 1.0
