Category: PHP

MySQL Reserved Words Will Break Your Queries Silently

When aliasing tables in SQL, avoid reserved keywords like if, select, where, etc. I recently debugged a query that failed with a cryptic syntax error, only to discover the alias if was the culprit. MySQL’s parser treats it as the IF() function, not your alias.

Instead of this broken query:

SELECT r.id, r.name
FROM reports r
LEFT JOIN report_data rd ON rd.report_id = r.id

If you accidentally use a reserved word like if as an alias:

-- This will fail with syntax error!
SELECT r.id, r.name
FROM reports r
LEFT JOIN report_data if ON if.report_id = r.id

Use something descriptive instead:

-- Safe and readable
SELECT r.id, r.name
FROM reports r
LEFT JOIN report_data rd ON rd.report_id = r.id

The extra characters are worth the clarity and reliability. This is especially tricky because some reserved words work fine as column names but fail as aliases, making the behavior inconsistent. Always consult MySQL’s reserved words list when choosing aliases, or better yet, just use short descriptive abbreviations that are clearly not keywords.

Pro tip: Modern IDEs will often highlight reserved words differently. Pay attention to that syntax coloring—it can save you debugging time.

I just spent 2 hours debugging a “works fine without this condition” bug, only to discover the most face-palm PHP mistake I’ve made in years: checking is_string('field_name') instead of is_string($data['field_name']).

The Bug

The code was supposed to merge currency codes from multiple sources, but only if a specific field contained a valid string:

$currencyCodes = [];

foreach ($batches as $batch) {
    // ... collect from batches ...
}

// Merge in historical data if it exists
if (is_string('historical_currencies')) {  // 🐛 BUG HERE
    $currencyCodes = array_unique(array_merge(
        $currencyCodes,
        explode(',', $batch['historical_currencies'])
    ));
}

See the problem? I’m checking if the literal string 'historical_currencies' is a string (it always is), not whether $batch['historical_currencies'] contains string data.

The Result

When $batch['historical_currencies'] was NULL:

1. The condition is_string('historical_currencies') evaluated to TRUE (literal strings are always strings)
2. The code tried to explode(',', NULL)
3. PHP 8.1+ throws a deprecation warning, but older versions silently return an empty array
4. Result: missing data, no error logs, mystery bug

The Fix

// Correct version
if (is_string($batch['historical_currencies'])) {
    $currencyCodes = array_unique(array_merge(
        $currencyCodes,
        explode(',', $batch['historical_currencies'])
    ));
}

Or better yet, use optional chaining with type safety:

if (!empty($batch['historical_currencies']) && is_string($batch['historical_currencies'])) {
    $currencyCodes = array_unique(array_merge(
        $currencyCodes,
        explode(',', $batch['historical_currencies'])
    ));
}

How This Slipped Through

1. No static analysis – PHPStan/Psalm would catch this immediately
2. Conditional always TRUE – so tests with valid data passed
3. Silent failure – no exception thrown, just missing results

The Lesson

Type-checking functions operate on values, not field names.

This applies to all type checks:

• is_array('items') ❌ vs is_array($data['items']) ✅
• is_numeric('total') ❌ vs is_numeric($data['total']) ✅
• is_null('deleted_at') ❌ vs is_null($data['deleted_at']) ✅

When in doubt, echo the value you’re checking. If you see a field name instead of actual data, you’re checking the wrong thing.

And seriously, run PHPStan. It would have caught this in 0.2 seconds.

Understanding PHP Database Extension Requirements

Different PHP frameworks and CMSs have specific database extension requirements. Installing pdo_mysql doesn’t automatically give you access to the legacy mysql or mysqli extensions — they’re separate modules.

Check What’s Installed

php -m | grep -i mysql
# Output might show:
pdo_mysql
mysqlnd

Verify Specific Extension Availability

<?php
if (extension_loaded('pdo')) {
    echo "PDO: Available\n";
}

if (extension_loaded('mysqli')) {
    echo "MySQLi: Available\n";
} else {
    echo "MySQLi: MISSING\n";
}

The Legacy Trap

Many legacy applications explicitly check for mysqli or mysql functions, even if PDO is available. When migrating to Docker or upgrading PHP versions, audit your Dockerfile’s docker-php-ext-install list against your framework’s actual requirements, not assumptions.

Example: Some CMS platforms check function_exists('mysqli_connect') during installation, which fails if only pdo_mysql is present.

The Fix

# Add to your Dockerfile
RUN docker-php-ext-install \
    pdo_mysql \
    mysqli \
    # ...other extensions

Don’t assume one database extension covers all use cases. Check the framework’s documentation and test explicitly.

Configuration arrays—whether from databases, API responses, or config files—can be incomplete or inconsistent. Directly accessing keys without checking for their existence causes Undefined array key errors in PHP 8+.

The Problem

This constructor assumes all configuration keys exist:

class ServiceCredentials
{
    private string $apiKey;
    private string $testApiKey;
    private string $secretToken;
    private string $testSecretToken;

    public function __construct(array $config, bool $isTest)
    {
        $this->apiKey = $config['api_key'];
        $this->testApiKey = $config['test']['api_key'];  // ❌ Crashes if test.api_key missing
        
        $this->secretToken = $config['secret_token'];
        $this->testSecretToken = $config['test']['secret_token'];  // ❌ Crashes if test.secret_token missing
    }
}

When $config['test'] is incomplete (missing api_key or secret_token), PHP 8 throws ErrorException: Undefined array key and your app crashes.

The Solution

Use null coalescing (??) to provide fallback values:

class ServiceCredentials
{
    private ?string $apiKey;
    private ?string $testApiKey;
    private ?string $secretToken;
    private ?string $testSecretToken;

    public function __construct(array $config, bool $isTest)
    {
        $this->apiKey = $config['api_key'] ?? null;
        $this->testApiKey = $config['test']['api_key'] ?? null;  // ✅ Graceful
        
        $this->secretToken = $config['secret_token'] ?? null;
        $this->testSecretToken = $config['test']['secret_token'] ?? null;  // ✅ Graceful
    }
    
    public function getApiKey(): string
    {
        if ($this->isTest && $this->testApiKey !== null) {
            return $this->testApiKey;
        }
        
        return $this->apiKey ?? throw new \RuntimeException('API key not configured');
    }
}

Why This Works

• Defensive coding — Configuration can be incomplete, user-managed, or legacy
• Graceful degradation — System doesn’t crash on initialization; fails at the point where the value is actually needed with better context
• Backwards compatible — Doesn’t break existing working configurations
• Type-safe — Nullable types (?string) signal that values might be absent

When to Use This

• Database-stored configuration (user-editable, schema evolves)
• API response deserialization (external APIs change)
• Multi-environment credentials (test/staging/production)
• Optional feature flags or settings

Rule of thumb: If a config key might be missing in production without it being a fatal error, use ?? + nullable types. If it’s always required, let it crash early with a clear error.

Discovered while debugging a production error where test credentials were incomplete in a third-party service integration. The fix: 4 lines changed, zero data migrations required.

Reusing variable names seems harmless until you’re deep in a debugging session wondering why your logs don’t match reality. Here’s a common trap and how to avoid it.

The Bug

You’re calling a payment gateway API. You build the request payload, send it, get a response. Standard stuff:

public function processPayment($amount, $currency)
{
    $data = [
        'amount' => $amount,
        'currency' => $currency,
        'merchant_id' => config('payment.merchant_id'),
    ];

    Log::info('Sending payment request', ['data' => $data]);

    $response = Http::post('https://api.paymentgateway.com/charge', $data);
    
    $data = $response->json();
    
    if ($data['status'] === 'success') {
        Log::info('Payment succeeded', ['data' => $data]);
        return $data['transaction_id'];
    }
    
    Log::error('Payment failed', ['data' => $data]);
    throw new PaymentException($data['message']);
}

Looks fine. But when you check logs after an error, you see:

Sending payment request: {"status":"failed","message":"Invalid merchant"}
Payment failed: {"status":"failed","message":"Invalid merchant"}

Wait, what? The “sending” log shows the response, not the request.

What Happened

You logged $data before the API call, but $data got overwritten by the response on line 11. Laravel’s logger is lazy—it doesn’t serialize variables immediately. When the log actually writes, $data now holds the response, not the request.

Result: both log entries show the same value (the response), making debugging a nightmare.

The Fix: Use Different Variable Names

Don’t reuse $data for two conceptually different things:

public function processPayment($amount, $currency)
{
    $payload = [
        'amount' => $amount,
        'currency' => $currency,
        'merchant_id' => config('payment.merchant_id'),
    ];

    Log::info('Sending payment request', ['payload' => $payload]);

    $response = Http::post('https://api.paymentgateway.com/charge', $payload);
    
    $responseData = $response->json();
    
    if ($responseData['status'] === 'success') {
        Log::info('Payment succeeded', ['response' => $responseData]);
        return $responseData['transaction_id'];
    }
    
    Log::error('Payment failed', ['response' => $responseData]);
    throw new PaymentException($responseData['message']);
}

Now your logs are correct:

Sending payment request: {"amount":1000,"currency":"USD","merchant_id":"123"}
Payment failed: {"status":"failed","message":"Invalid merchant"}

Why This Matters

• Logs are your debugging lifeline. If they lie, you’re lost.
• $data, $result, $response — these names are magnets for reuse.
• Lazy logging (common in Laravel, Monolog, etc.) means variable references get resolved later, not when you call Log::info().

Rule of Thumb

If you’re logging a variable, don’t overwrite it afterward. Give each conceptual “thing” its own name: $request, $response, $payload, $result.

Debugging is hard enough. Don’t let your variable names lie to you.

Five classes. Same trait. Same boolean helper method. Every single one had the condition inverted. And nobody noticed for over a year.

The Setup

A reporting module had several column classes that all shared a trait:

trait ChecksEligibility
{
    private function isEligible(Order $order): bool
    {
        $exemptTypes = config('reports.exempt_types', []);
        return !in_array($order->type, $exemptTypes);
    }
}

Simple enough. Returns true for most orders, false for exempt ones. Five different report column classes used this trait to decide whether to apply a calculation:

class TotalBeforeAdjustment extends ReportColumn
{
    use ChecksEligibility;

    public function getValue(Order $order): float
    {
        if (!$this->isEligible($order)) {
            return $order->total / 1.09; // Remove the 9% adjustment
        }

        return $order->total; // No adjustment needed
    }
}

Spot the bug?

The Inversion

The ! negation is backwards. For eligible orders (the common case), it should apply the calculation. For exempt orders, it should skip it. But the ! flips the logic — exempt orders get calculated, eligible ones don’t.

Every class using this trait had the same inverted condition. Somewhere, the first developer misread what isEligible returned, and everyone else copy-pasted the pattern.

How to Find It: Empirical Debugging

Don’t trust your reading of the code. Run it.

// In artisan tinker
$order = Order::find(12345); // Known eligible order
$column = new TotalBeforeAdjustment();
$column->getValue($order);
// Expected: 100.00 (no adjustment)
// Got: 91.74 (adjustment applied!)

$exemptOrder = Order::find(67890); // Known exempt order
$column->getValue($exemptOrder);
// Expected: 91.74 (adjustment applied)
// Got: 100.00 (no adjustment!)

Two test cases. One eligible, one exempt. The outputs are swapped. Bug confirmed in under 30 seconds.

The Fix

// Before (wrong)
if (!$this->isEligible($order)) {

// After (correct)
if ($this->isEligible($order)) {

One character removed. Repeated across 5 classes.

Why It Hid for So Long

Three reasons:

1. Low-traffic feature. The report was used monthly by a handful of people. Nobody cross-checked the numbers against source data.
2. Method naming confusion. “isEligible” could reasonably mean either “should we apply the calculation” or “is this order in the standard category”. The name was technically correct but invited misinterpretation.
3. Copy-paste propagation. Once the first class got it wrong, every subsequent class copied the same pattern. Consistency made the bug look intentional.

Prevention

When you share boolean helpers via traits:

• Name the method for what the CALLER does with it. shouldApplyAdjustment() is harder to invert than isEligible().
• Add a unit test with both cases. One eligible, one exempt. Takes 2 minutes, catches inversions immediately.
• Be suspicious of ! before trait methods. If every consumer negates the return value, either the method name is misleading or the logic is inverted.

The takeaway: Boolean bugs don’t crash your app — they silently produce wrong data. The only reliable way to catch them is to test with known inputs and verify the outputs match your expectations. Read the code, then run the code.

You’re reading a method and hit something like this:

$results = $categories->map(function ($category) use ($config) {
    return $category->items->filter(function ($item) use ($config) {
        return $item->variants->map(function ($variant) use ($config, $item) {
            $basePrice = $variant->price * $config->multiplier;
            $discount = $item->hasDiscount() ? $basePrice * $item->discountRate() : 0;
            return [
                'sku' => $variant->sku,
                'final_price' => $basePrice - $discount,
                'label' => "{$item->name} — {$variant->size}",
            ];
        })->filter(fn ($v) => $v['final_price'] > 0);
    })->flatten(1);
})->flatten(1);

Three levels deep. Four use imports. By the time you reach the inner logic, you’ve lost the context of the outer layers. This is the closure nesting trap.

Extract and Name

Pull the inner closure into a private method with a name that describes what it does:

$results = $categories->map(function ($category) use ($config) {
    return $category->items
        ->flatMap(fn ($item) => $this->buildVariantPrices($item, $config))
        ->filter(fn ($v) => $v['final_price'] > 0);
})->flatten(1);

private function buildVariantPrices(Item $item, PricingConfig $config): Collection
{
    return $item->variants->map(function ($variant) use ($config, $item) {
        $basePrice = $variant->price * $config->multiplier;
        $discount = $item->hasDiscount() ? $basePrice * $item->discountRate() : 0;

        return [
            'sku' => $variant->sku,
            'final_price' => $basePrice - $discount,
            'label' => "{$item->name} — {$variant->size}",
        ];
    });
}

Why This Works

The parent chain becomes scannable. You can read the top-level flow — map categories, build variant prices, filter positives — without parsing the inner logic. The method name tells you what happens; the method body tells you how.

Type hints become possible. The extracted method can declare its parameter and return types. The closure couldn’t.

Testing gets easier. You can test buildVariantPrices() directly with a mock item and config, without setting up the full category→item→variant hierarchy.

The Rule of Thumb

If a closure:

• Has more than 2 use imports
• Is nested inside another closure
• Contains more than 5 lines of logic

…extract it into a named private method. The method name acts as documentation, and the signature acts as a contract.

The takeaway: Closures are great for simple transformations. But when they nest, they become anonymous complexity. Give them a name, and your code reads like an outline instead of a puzzle.