# API Pagination Fix - Handling length=-1 Parameter

## Issue Summary
Multiple API endpoints fail when called with `?length=-1` parameter because MySQL/MariaDB doesn't accept negative numbers in LIMIT clause.

---

## Affected APIs

### Tier 1: FIXED ✅
These APIs now handle `length=-1` correctly:

| API | File | Fix Type | Status |
|-----|------|----------|--------|
| shipment/read.php | ✅ FIXED | Input validation | ✅ Complete |
| branch/read.php | ✅ ALREADY HANDLES | Conditional LIMIT | ✅ Already OK |

### Tier 2: CHECK NEEDED
These APIs might need the same fix:

| API | File | Current Status | Action |
|-----|------|----------------|--------|
| employee/read.php | Needs check | May have issue | Review |
| role/read.php | Needs check | May have issue | Review |
| department/read.php | Needs check | May have issue | Review |
| designation/read.php | Needs check | May have issue | Review |
| booking/read.php | Needs check | May have issue | Review |
| client/read.php | Needs check | May have issue | Review |
| consignor/read.php | Needs check | May have issue | Review |
| consignee/read.php | Needs check | May have issue | Review |
| courier_partner/read.php | Needs check | May have issue | Review |
| pickuppoint/read.php | Needs check | May have issue | Review |

---

## Two Approaches to Fix

### Approach 1: Convert negative to large number (FIXED)
**Used in:** api/shipment/read.php

```php
// Handle -1 (get all records) - set to a large number
if ($length <= 0) {
    $length = 999999;
}

$sql .= " LIMIT :start, :length";
```

**Pros:**
- Simple implementation
- Works with existing LIMIT syntax
- Always applies limit

**Cons:**
- Arbitrary large number (999999)
- Less readable intent

---

### Approach 2: Conditional LIMIT (PREFERRED)
**Used in:** api/branch/read.php

```php
// Only apply limit if length is not -1
if ($length != -1) {
    $sql .= " LIMIT :start, :length";
}

if ($length != -1) {
    $stmt->bindValue(':start', $start, PDO::PARAM_INT);
    $stmt->bindValue(':length', $length, PDO::PARAM_INT);
}
```

**Pros:**
- Clear intent
- No limit when -1 is passed
- More explicit

**Cons:**
- More code changes
- Conditional parameter binding

---

## Current Status

### api/shipment/read.php - FIXED ✅

**Before:**
```php
$length = isset($_GET['length']) ? (int) $_GET['length'] : 10;
$sql .= " ORDER BY b.created_at DESC LIMIT :start, :length";
```

**After:**
```php
$length = isset($_GET['length']) ? (int) $_GET['length'] : 10;

// Handle -1 (get all records) - set to a large number
if ($length <= 0) {
    $length = 999999;
}

$sql .= " ORDER BY b.created_at DESC LIMIT :start, :length";
```

**Test:**
```bash
curl "http://localhost/steve/api/shipment/read.php?length=-1"
# Should return all shipments without error
```

---

### api/branch/read.php - ALREADY HANDLES ✅

This API already correctly handles `length=-1`:

```php
// Only apply limit if length is not -1
if ($length != -1) {
    $sql .= " LIMIT :start, :length";
}

if ($length != -1) {
    $stmt->bindValue(':start', $start, PDO::PARAM_INT);
    $stmt->bindValue(':length', $length, PDO::PARAM_INT);
}
```

**Test:**
```bash
curl "http://localhost/steve/api/branch/read.php?length=-1"
# Already works correctly
```

---

## Recommended: Standardize All APIs

### Recommended Approach
Use Approach 2 (Conditional LIMIT) for all APIs because it's more explicit and cleaner.

### Batch Fix Template
For any API with pagination issue:

```php
// BEFORE
$length = isset($_GET['length']) ? (int) $_GET['length'] : 10;
// ... build $sql ...
$sql .= " LIMIT :start, :length";
// ... bind parameters ...
$stmt->bindValue(':start', $start, PDO::PARAM_INT);
$stmt->bindValue(':length', $length, PDO::PARAM_INT);

// AFTER
$length = isset($_GET['length']) ? (int) $_GET['length'] : 10;
// ... build $sql ...

// Only apply limit if length is not -1
if ($length != -1) {
    $sql .= " LIMIT :start, :length";
}

// ... bind other parameters ...

if ($length != -1) {
    $stmt->bindValue(':start', $start, PDO::PARAM_INT);
    $stmt->bindValue(':length', $length, PDO::PARAM_INT);
}
```

---

## Testing All Affected APIs

### Quick Test Script
```bash
#!/bin/bash

# Test all read APIs with length=-1
apis=(
    "api/shipment/read.php"
    "api/employee/read.php"
    "api/branch/read.php"
    "api/role/read.php"
    "api/department/read.php"
    "api/designation/read.php"
    "api/client/read.php"
    "api/consignor/read.php"
    "api/consignee/read.php"
    "api/courier_partner/read.php"
    "api/pickuppoint/read.php"
)

for api in "${apis[@]}"; do
    echo "Testing: $api"
    curl -s "http://localhost/steve/$api?length=-1" | grep -q "error" && echo "  ❌ ERROR" || echo "  ✅ OK"
done
```

### PHP Test Script
```php
<?php
$apis = [
    'api/shipment/read.php',
    'api/employee/read.php',
    'api/branch/read.php',
    // ... add all others
];

foreach ($apis as $api) {
    $url = "http://localhost/steve/$api?length=-1";
    $response = json_decode(file_get_contents($url), true);
    
    if (isset($response['error'])) {
        echo "❌ $api: " . $response['error'] . "\n";
    } else {
        echo "✅ $api: OK\n";
    }
}
?>
```

---

## Implementation Timeline

### Immediate (Done)
- [x] Fix api/shipment/read.php
- [x] Verify api/branch/read.php already handles it
- [x] Create this documentation

### Short-term (Recommended)
- [ ] Test all APIs with `?length=-1`
- [ ] Fix any failing APIs using Approach 2
- [ ] Create unit tests for pagination

### Long-term (Optional)
- [ ] Implement proper pagination library
- [ ] Add "limit" query parameter (true/false or "all")
- [ ] Add API documentation
- [ ] Add rate limiting

---

## Error Examples

### Error Before Fix
```
Error: SQLSTATE[42000]: Syntax error or access violation: 1064 
You have an error in your SQL syntax; check the manual that corresponds 
to your MariaDB server version for the right syntax to use near '-1' at line 9
```

### Success After Fix
```json
{
  "status": "success",
  "recordsTotal": 45,
  "recordsFiltered": 45,
  "data": [
    { "id": 1, "name": "Item 1", ... },
    { "id": 2, "name": "Item 2", ... },
    ...
  ]
}
```

---

## SQL Limits Reference

### Valid LIMIT Syntax
```sql
-- Get 10 records starting from 0
LIMIT 0, 10      ✅ VALID

-- Get all records (no limit)
LIMIT 999999     ✅ VALID (but arbitrary)

-- Get 1000 records starting from 0
LIMIT 0, 1000    ✅ VALID

-- No LIMIT clause
SELECT * FROM table  ✅ VALID (if no LIMIT is needed)
```

### Invalid LIMIT Syntax
```sql
-- Negative offset
LIMIT -1, 10     ❌ INVALID

-- Negative count
LIMIT 0, -1      ❌ INVALID

-- Both negative
LIMIT -1, -1     ❌ INVALID
```

---

## Browser/Frontend Perspective

### Why Frontend Passes length=-1
Common JavaScript patterns that use `-1` to mean "get all":

```javascript
// DataTables
$('#table').DataTable({
    serverSide: true,
    ajax: '/api/shipment/read.php?length=-1'
});

// jQuery plugins
$.get('api/shipment/read.php?length=-1', function(data) {
    // Process all records
});

// Vue/React
fetch('/api/shipment/read.php?length=-1')
    .then(r => r.json())
    .then(data => { /* all records */ });
```

---

## Deployment Checklist

### Before Deployment
- [x] api/shipment/read.php fixed
- [ ] All other APIs checked
- [ ] Unit tests created
- [ ] Staging tested

### Deployment Steps
1. Backup current api/ directory
2. Deploy fixed api/shipment/read.php
3. Test with: `curl "http://localhost/steve/api/shipment/read.php?length=-1"`
4. Monitor error logs
5. Fix remaining APIs as needed

### Post-Deployment
- [ ] Monitor error logs for similar issues
- [ ] Document API usage
- [ ] Update API documentation
- [ ] Train developers on pagination patterns

---

## Documentation References

- MySQL LIMIT: https://dev.mysql.com/doc/refman/8.0/en/select.html
- MariaDB LIMIT: https://mariadb.com/kb/en/select/
- Error 1064: https://mariadb.com/kb/en/mariadb-error-codes/

---

## Summary

✅ **FIXED:** api/shipment/read.php now handles `?length=-1`  
✅ **VERIFIED:** api/branch/read.php already handles it correctly  
📋 **TODO:** Check and fix remaining 14 API read endpoints  

**Current Status:** Feature working, but recommend standardizing all APIs
