Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/soundstudiopro.com/public_html/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
/**
 * Quick diagnostic script to check purchases in database
 * Run this to see if purchases are being recorded
 */

session_start();
require_once 'config/database.php';

// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
    die("Admin access required");
}

$pdo = getDBConnection();

echo "<h2>Purchase Database Diagnostic</h2>";
echo "<style>body { font-family: Arial; padding: 20px; background: #1a1a1a; color: white; } table { border-collapse: collapse; width: 100%; margin: 20px 0; } th, td { border: 1px solid #444; padding: 10px; text-align: left; } th { background: #667eea; }</style>";

// 1. Check if track_purchases table exists
echo "<h3>1. Table Check</h3>";
try {
    $table_check = $pdo->query("SHOW TABLES LIKE 'track_purchases'");
    if ($table_check->rowCount() > 0) {
        echo "<p style='color: #48bb78;'>✓ track_purchases table exists</p>";
    } else {
        echo "<p style='color: #e53e3e;'>✗ track_purchases table does NOT exist</p>";
        exit;
    }
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
    exit;
}

// 2. Check table structure
echo "<h3>2. Table Structure</h3>";
try {
    $columns = $pdo->query("SHOW COLUMNS FROM track_purchases");
    echo "<table>";
    echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($col['Field']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Type']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Null']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Key']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Default'] ?? 'NULL') . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

// 3. Count total purchases
echo "<h3>3. Total Purchases Count</h3>";
try {
    $total = $pdo->query("SELECT COUNT(*) as total FROM track_purchases")->fetch(PDO::FETCH_ASSOC);
    echo "<p><strong>Total purchases in database:</strong> <span style='color: #48bb78; font-size: 24px;'>{$total['total']}</span></p>";
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

// 4. Check for Kat Zen specifically
echo "<h3>4. Kat Zen Purchases</h3>";
try {
    $kat_zen = $pdo->prepare("
        SELECT id, name, email 
        FROM users 
        WHERE name LIKE '%Kat%Zen%' OR name LIKE '%kat%zen%' OR email LIKE '%kat%zen%'
    ");
    $kat_zen->execute();
    $users = $kat_zen->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($users)) {
        echo "<p style='color: #e53e3e;'>✗ No user found matching 'Kat Zen'</p>";
        echo "<p>Searching for similar names...</p>";
        $all_users = $pdo->query("SELECT id, name, email FROM users WHERE name LIKE '%kat%' OR name LIKE '%zen%' LIMIT 10")->fetchAll();
        if (!empty($all_users)) {
            echo "<table>";
            echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";
            foreach ($all_users as $u) {
                echo "<tr><td>{$u['id']}</td><td>{$u['name']}</td><td>{$u['email']}</td></tr>";
            }
            echo "</table>";
        }
    } else {
        foreach ($users as $user) {
            echo "<p style='color: #48bb78;'>✓ Found user: <strong>{$user['name']}</strong> (ID: {$user['id']}, Email: {$user['email']})</p>";
            
            // Check purchases for this user
            $purchases = $pdo->prepare("
                SELECT 
                    tp.*,
                    mt.title as track_title,
                    mt.price as track_price
                FROM track_purchases tp
                LEFT JOIN music_tracks mt ON tp.track_id = mt.id
                WHERE tp.user_id = ?
                ORDER BY tp.purchase_date DESC
            ");
            $purchases->execute([$user['id']]);
            $user_purchases = $purchases->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<p><strong>Purchases for this user:</strong> " . count($user_purchases) . "</p>";
            
            if (!empty($user_purchases)) {
                echo "<table>";
                echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Track Title</th><th>Price Paid</th><th>Purchase Date</th><th>Payment Method</th></tr>";
                foreach ($user_purchases as $p) {
                    $payment_method = isset($p['payment_method']) ? $p['payment_method'] : 'N/A';
                    echo "<tr>";
                    echo "<td>{$p['id']}</td>";
                    echo "<td>{$p['track_id']}</td>";
                    echo "<td>" . htmlspecialchars($p['track_title'] ?? 'Unknown') . "</td>";
                    echo "<td>\${$p['price_paid']}</td>";
                    echo "<td>{$p['purchase_date']}</td>";
                    echo "<td>{$payment_method}</td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<p style='color: #e53e3e;'>✗ No purchases found for this user</p>";
            }
        }
    }
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

// 5. Show recent purchases (all users)
echo "<h3>5. Recent Purchases (All Users - Last 10)</h3>";
try {
    $recent = $pdo->query("
        SELECT 
            tp.*,
            u.name as buyer_name,
            u.email as buyer_email,
            mt.title as track_title
        FROM track_purchases tp
        LEFT JOIN users u ON tp.user_id = u.id
        LEFT JOIN music_tracks mt ON tp.track_id = mt.id
        ORDER BY tp.purchase_date DESC
        LIMIT 10
    ");
    $recent_purchases = $recent->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($recent_purchases)) {
        echo "<p style='color: #e53e3e;'>✗ No purchases found in database at all</p>";
    } else {
        echo "<table>";
        echo "<tr><th>ID</th><th>Buyer</th><th>Track</th><th>Price</th><th>Date</th><th>Payment Method</th></tr>";
        foreach ($recent_purchases as $p) {
            $payment_method = isset($p['payment_method']) ? $p['payment_method'] : 'N/A';
            echo "<tr>";
            echo "<td>{$p['id']}</td>";
            echo "<td>" . htmlspecialchars($p['buyer_name'] ?? 'Unknown') . "<br><small>" . htmlspecialchars($p['buyer_email'] ?? '') . "</small></td>";
            echo "<td>" . htmlspecialchars($p['track_title'] ?? 'Unknown') . "</td>";
            echo "<td>\${$p['price_paid']}</td>";
            echo "<td>{$p['purchase_date']}</td>";
            echo "<td>{$payment_method}</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

// 6. Check track 182 specifically
echo "<h3>6. Track 182 Purchases</h3>";
try {
    $track_182 = $pdo->prepare("
        SELECT 
            tp.*,
            u.name as buyer_name,
            u.email as buyer_email
        FROM track_purchases tp
        LEFT JOIN users u ON tp.user_id = u.id
        WHERE tp.track_id = 182
        ORDER BY tp.purchase_date DESC
    ");
    $track_182->execute();
    $track_purchases = $track_182->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p><strong>Purchases of track 182:</strong> " . count($track_purchases) . "</p>";
    
    if (!empty($track_purchases)) {
        echo "<table>";
        echo "<tr><th>Purchase ID</th><th>Buyer</th><th>Email</th><th>Price Paid</th><th>Purchase Date</th></tr>";
        foreach ($track_purchases as $p) {
            echo "<tr>";
            echo "<td>{$p['id']}</td>";
            echo "<td>" . htmlspecialchars($p['buyer_name'] ?? 'Unknown') . "</td>";
            echo "<td>" . htmlspecialchars($p['buyer_email'] ?? '') . "</td>";
            echo "<td>\${$p['price_paid']}</td>";
            echo "<td>{$p['purchase_date']}</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: #e53e3e;'>✗ No purchases found for track 182</p>";
    }
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

// 7. Check sales table
echo "<h3>7. Sales Table Check</h3>";
try {
    $sales_count = $pdo->query("SELECT COUNT(*) as total FROM sales")->fetch(PDO::FETCH_ASSOC);
    echo "<p><strong>Total sales in database:</strong> <span style='color: #48bb78; font-size: 24px;'>{$sales_count['total']}</span></p>";
    
    // Check for track 182 sales
    $track_182_sales = $pdo->prepare("
        SELECT 
            s.*,
            u.name as buyer_name
        FROM sales s
        LEFT JOIN users u ON s.buyer_id = u.id
        WHERE s.track_id = 182
        ORDER BY s.created_at DESC
    ");
    $track_182_sales->execute();
    $sales = $track_182_sales->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p><strong>Sales of track 182:</strong> " . count($sales) . "</p>";
    
    if (!empty($sales)) {
        echo "<table>";
        echo "<tr><th>Sale ID</th><th>Buyer</th><th>Amount</th><th>Date</th><th>Revenue To</th></tr>";
        foreach ($sales as $s) {
            echo "<tr>";
            echo "<td>{$s['id']}</td>";
            echo "<td>" . htmlspecialchars($s['buyer_name'] ?? 'Unknown') . "</td>";
            echo "<td>\${$s['amount']}</td>";
            echo "<td>{$s['created_at']}</td>";
            echo "<td>{$s['revenue_recipient']}</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "<p style='color: #e53e3e;'>Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";
echo "<p><a href='/admin.php?tab=purchases' style='color: #667eea;'>← Back to Purchases Admin</a></p>";
?>