# SQL & Database Query Skill

> **Technology:** MySQL/MariaDB via WordPress $wpdb with prepared statements

---

## Purpose

This skill covers safe database query patterns, SQL injection prevention, and WordPress $wpdb usage for the Comments Press Zone plugin.

---

## Critical Security Rule

**ALWAYS use `$wpdb->prepare()` for dynamic queries. ZERO TOLERANCE for SQL injection.**

---

## Table Naming Convention

```php
global $wpdb;
$table_likes = $wpdb->prefix . 'presszone_comments_likes';
$table_reports = $wpdb->prefix . 'presszone_comments_reports';
$table_warnings = $wpdb->prefix . 'presszone_comments_warnings';
$table_users_extended = $wpdb->prefix . 'presszone_comments_users_extended';
$table_alerts = $wpdb->prefix . 'presszone_comments_alerts';
$table_audit_log = $wpdb->prefix . 'presszone_comments_audit_log';
```

**Actual table names:** `wp_presszone_comments_*`

---

## Query Patterns

### SELECT Single Value

```php
global $wpdb;
$table = $wpdb->prefix . 'presszone_comments_likes';

$count = $wpdb->get_var($wpdb->prepare(
    "SELECT COUNT(*) FROM $table WHERE comment_id = %d AND type = %s",
    $comment_id,
    'upvote'
));
```

### SELECT Single Row

```php
$vote = $wpdb->get_row($wpdb->prepare(
    "SELECT id, type, created_at FROM $table WHERE comment_id = %d AND user_id = %d",
    $comment_id,
    $user_id
));

// Access: $vote->id, $vote->type, $vote->created_at
```

### SELECT Multiple Rows

```php
$votes = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM $table WHERE comment_id = %d ORDER BY created_at DESC",
    $comment_id
), ARRAY_A);

// Returns array of associative arrays
foreach ($votes as $vote) {
    echo $vote['user_id'];
}
```

### INSERT

```php
$result = $wpdb->insert(
    $table,
    [
        'comment_id' => $comment_id,
        'user_id' => get_current_user_id(),
        'type' => $type,
        'ip_address' => $ip_address,
    ],
    ['%d', '%d', '%s', '%s']
);

if ($result === false) {
    error_log('Insert failed: ' . $wpdb->last_error);
}

$insert_id = $wpdb->insert_id;
```

### UPDATE

```php
$result = $wpdb->update(
    $table,
    ['type' => $new_type], // Data
    ['id' => $vote_id],    // Where
    ['%s'],                // Data format
    ['%d']                 // Where format
);

if ($result === false) {
    error_log('Update failed: ' . $wpdb->last_error);
}
```

### DELETE

```php
$result = $wpdb->delete(
    $table,
    ['id' => $vote_id],
    ['%d']
);

if ($result === false) {
    error_log('Delete failed: ' . $wpdb->last_error);
}
```

---

## Complex Queries

### JOIN with WordPress Users

```php
$results = $wpdb->get_results($wpdb->prepare(
    "SELECT u.ID, u.display_name, COUNT(l.id) as vote_count
     FROM {$wpdb->users} u
     INNER JOIN $table l ON u.ID = l.user_id
     WHERE l.type = %s AND l.created_at > %s
     GROUP BY u.ID
     ORDER BY vote_count DESC
     LIMIT %d",
    'upvote',
    gmdate('Y-m-d H:i:s', strtotime('-30 days')),
    10
), ARRAY_A);
```

### Aggregation Query

```php
$stats = $wpdb->get_row($wpdb->prepare(
    "SELECT 
        COUNT(*) as total_votes,
        SUM(CASE WHEN type = 'upvote' THEN 1 ELSE 0 END) as upvotes,
        SUM(CASE WHEN type = 'downvote' THEN 1 ELSE 0 END) as downvotes
     FROM $table
     WHERE comment_id = %d",
    $comment_id
));
```

### Subquery

```php
$top_commenters = $wpdb->get_results($wpdb->prepare(
    "SELECT user_id, COUNT(*) as comment_count
     FROM {$wpdb->comments}
     WHERE user_id IN (
         SELECT DISTINCT user_id 
         FROM $table 
         WHERE type = %s
     )
     GROUP BY user_id
     ORDER BY comment_count DESC
     LIMIT %d",
    'upvote',
    10
), ARRAY_A);
```

---

## LIKE Queries (Wildcard Search)

```php
// CRITICAL: Use esc_like() to escape wildcards
$search = '%' . $wpdb->esc_like($search_term) . '%';

$results = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM {$wpdb->comments} WHERE comment_content LIKE %s",
    $search
), ARRAY_A);
```

---

## Transactions (InnoDB Required)

```php
global $wpdb;

// Start transaction
$wpdb->query('START TRANSACTION');

try {
    $wpdb->insert($table1, $data1, $format1);
    $wpdb->insert($table2, $data2, $format2);
    
    // Commit if all succeed
    $wpdb->query('COMMIT');
} catch (\Exception $e) {
    // Rollback on error
    $wpdb->query('ROLLBACK');
    error_log('Transaction failed: ' . $e->getMessage());
}
```

---

## Placeholder Reference

| Placeholder | Type | Example |
|-------------|------|---------|
| `%d` | Integer | `$wpdb->prepare("...id = %d", 123)` |
| `%s` | String | `$wpdb->prepare("...name = %s", 'John')` |
| `%f` | Float | `$wpdb->prepare("...price = %f", 19.99)` |

---

## Vote Query Pattern (Toggle Logic)

```php
public function toggle_vote(int $comment_id, string $type): array {
    global $wpdb;
    $table = $wpdb->prefix . 'presszone_comments_likes';
    $user_id = get_current_user_id();
    $ip_address = $this->get_user_ip();

    // Check for existing vote
    $existing = $wpdb->get_row($wpdb->prepare(
        "SELECT id, type FROM $table WHERE comment_id = %d AND (user_id = %d OR ip_address = %s)",
        $comment_id,
        $user_id,
        $ip_address
    ));

    if ($existing) {
        if ($existing->type === $type) {
            // Remove vote (toggle off)
            $wpdb->delete($table, ['id' => $existing->id], ['%d']);
            $action = 'removed';
        } else {
            // Change vote type
            $wpdb->update($table, ['type' => $type], ['id' => $existing->id], ['%s'], ['%d']);
            $action = 'updated';
        }
    } else {
        // Add new vote
        $wpdb->insert($table, [
            'comment_id' => $comment_id,
            'user_id' => $user_id,
            'type' => $type,
            'ip_address' => $ip_address,
        ], ['%d', '%d', '%s', '%s']);
        $action = 'added';
    }

    // Get updated counts
    $upvotes = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table WHERE comment_id = %d AND type = 'upvote'",
        $comment_id
    ));
    $downvotes = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table WHERE comment_id = %d AND type = 'downvote'",
        $comment_id
    ));

    return [
        'action' => $action,
        'upvotes' => $upvotes,
        'downvotes' => $downvotes,
    ];
}
```

---

## Common Mistakes to Avoid

| Mistake | Fix |
|---------|-----|
| No `$wpdb->prepare()` | ALWAYS use prepare() for dynamic queries |
| Hardcoded table names | Use `$wpdb->prefix . 'presszone_comments_*'` |
| Missing format array in insert/update | Specify `['%d', '%s']` |
| Not escaping LIKE wildcards | Use `$wpdb->esc_like()` |
| Using deprecated functions | Use `$wpdb->prepare()`, not `mysqli_*` |
| Not checking query results | Check `=== false` for errors |

---

## WordPress Database Globals

```php
global $wpdb;

// WordPress tables
$wpdb->users           // wp_users
$wpdb->posts           // wp_posts
$wpdb->comments        // wp_comments
$wpdb->options         // wp_options
$wpdb->usermeta        // wp_usermeta
$wpdb->commentmeta     // wp_commentmeta

// Custom table prefix
$wpdb->prefix          // Default: 'wp_'
```

---

## Testing Checklist

- [ ] All dynamic queries use `$wpdb->prepare()`
- [ ] All table names use `$wpdb->prefix`
- [ ] Format arrays specified for insert/update
- [ ] LIKE queries use `$wpdb->esc_like()`
- [ ] Query errors checked and logged
- [ ] No SQL injection vulnerabilities