Dynamic Tables Query and Manual Query

<?php
// PHP Backend Logic
// This script handles all database interactions.
// It is designed to be self-contained within the HTML file.

// --- Configuration and Error Reporting ---
// Enable MySQLi exceptions for better error handling.
// This allows us to use try-catch blocks for database operations.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Define a constant for the error log file path
define('LOG_FILE_PATH', 'error_log.txt');

// --- Centralized Error Logging Function ---
// This function writes detailed error messages to the log file.
function log_error($message, $file, $line) {
    // OLD: error_log($message);
    // NEW: Log the error with a timestamp and file/line number
    $timestamp = date('d-M-Y H:i:s');
    $log_message = sprintf("[%s] [%s:%s] %s\n", $timestamp, $file, $line, $message);
    error_log($log_message, 3, LOG_FILE_PATH);
}

// Database connection details
// IMPORTANT: Replace with your actual credentials
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "test"; // The database name as specified in the request

// Establish a connection to the database
try {
    $mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);
    // Set character set to prevent encoding issues
    $mysqli->set_charset("utf8");
} catch (mysqli_sql_exception $e) {
    // Log the connection error and provide a user-friendly message
    log_error("Database connection failed: " . $e->getMessage(), __FILE__, __LINE__);
    die("An error occurred while connecting to the database. Please check the logs for more details.");
}

// --- Function to safely escape user input ---
// This function trims whitespace and escapes special characters to prevent SQL injection.
function clean_input($data) {
    global $mysqli;
    // OLD: $data = trim($data);
    // NEW: Ensure data is a string before trimming and escaping
    $data = is_string($data) ? trim($data) : $data;
    $data = mysqli_real_escape_string($mysqli, $data);
    return $data;
}

// --- AJAX action handler ---
// This part of the code responds to requests from the frontend.
try {
    if (isset($_POST['action'])) {
        $action = clean_input($_POST['action']);

        // Action 1: Fetch columns and key info for a selected table
        if ($action === 'get_columns') {
            $table_name = isset($_POST['table']) ? clean_input($_POST['table']) : '';
            if (empty($table_name)) {
                die(json_encode(['error' => 'No table specified.']));
            }

            $response = [
                'columns' => [],
                'primary_key' => '',
                'auto_increment' => ''
            ];

            try {
                // Get column details and primary key
                $result = $mysqli->query("DESCRIBE `$table_name`");
                if ($result) {
                    while ($row = $result->fetch_assoc()) {
                        $column_name = $row['Field'];
                        $response['columns'][] = $column_name;
                        if ($row['Key'] === 'PRI') {
                            $response['primary_key'] = $column_name;
                        }
                    }
                    $result->free();
                }

                // Get auto_increment property from SHOW CREATE TABLE
                $result = $mysqli->query("SHOW CREATE TABLE `$table_name`");
                if ($result) {
                    $row = $result->fetch_assoc();
                    $create_table_sql = $row['Create Table'];
                    if (preg_match('/`[^`]+`\s+INT\([^`]+\)\s+NOT NULL\s+AUTO_INCREMENT/', $create_table_sql, $matches)) {
                        $auto_increment_column = str_replace('`', '', substr($matches[0], 0, strpos($matches[0], ' ')));
                        $response['auto_increment'] = $auto_increment_column;
                    }
                    $result->free();
                }

                header('Content-Type: application/json');
                echo json_encode($response);
            } catch (mysqli_sql_exception $e) {
                // Log the query error
                log_error("Failed to get columns for table '$table_name': " . $e->getMessage(), __FILE__, __LINE__);
                die(json_encode(['error' => 'An error occurred while fetching table information.']));
            }
            exit;
        }

        // Action 2: Execute a user-defined query and display results
        if ($action === 'execute_query') {
            // OLD: $query = isset($_POST['query']) ? trim($_POST['query']) : '';
            // NEW: Apply trim to the raw query
            $query = isset($_POST['query']) ? trim($_POST['query']) : '';

            // Note: This feature allows the user to run arbitrary SQL.
            // This is a known risk, and the user must be trusted.
            // Prepared statements are not used here as the query itself is dynamic.

            $html = '';
            try {
                $result = $mysqli->query($query);
                if ($result === false) {
                    // This case should be caught by the exception, but added as a failsafe
                    throw new mysqli_sql_exception("Query returned false result.");
                }

                $html .= '<div class="table-responsive mt-3">';
                $html .= '<table class="table table-striped table-bordered table-hover">';
                $html .= '<thead class="table-dark"><tr>';

                // Fetch headers
                $field_info = $result->fetch_fields();
                $headers = [];
                foreach ($field_info as $val) {
                    $headers[] = $val->name;
                    $html .= '<th>' . htmlspecialchars($val->name) . '</th>';
                }
                $html .= '</tr></thead><tbody>';

                // Fetch and display data
                $primary_key = isset($_POST['primary_key']) ? trim($_POST['primary_key']) : '';
                while ($row = $result->fetch_assoc()) {
                    $pk_value = isset($row[$primary_key]) ? htmlspecialchars($row[$primary_key]) : '';
                    $html .= '<tr data-pk-value="' . $pk_value . '">';
                    foreach ($headers as $header) {
                        $html .= '<td contenteditable="true" data-col-name="' . htmlspecialchars($header) . '">' . htmlspecialchars($row[$header]) . '</td>';
                    }
                    $html .= '</tr>';
                }
                $html .= '</tbody></table></div>';
                $html .= '<button id="updateChangesBtn" class="btn btn-primary mt-3 me-2">Save All Changes</button>';
                $result->free();
            } catch (mysqli_sql_exception $e) {
                // Log the specific query error
                log_error("Query execution failed: '$query'. Error: " . $e->getMessage(), __FILE__, __LINE__);
                // OLD: $html = '<div class="alert alert-danger mt-3" role="alert">Query error: ' . $mysqli->error . '</div>';
                // NEW: A more polite and informative error message for the user
                $html = '<div class="alert alert-danger mt-3" role="alert">An error occurred while executing your query. Please double-check the syntax.</div>';
            }

            echo $html;
            exit;
        }

        // Action 3: Update records in the database
        if ($action === 'update_records') {
            $table_name = isset($_POST['table']) ? clean_input($_POST['table']) : '';
            $primary_key = isset($_POST['primary_key']) ? clean_input($_POST['primary_key']) : '';
            $data = isset($_POST['data']) ? json_decode($_POST['data'], true) : [];

            if ($data === null) {
                log_error("Invalid JSON data received for update.", __FILE__, __LINE__);
                echo json_encode(['success' => false, 'message' => 'Invalid data received from the client.']);
                exit;
            }

            if (empty($table_name) || empty($primary_key)) {
                log_error("Table name or primary key is missing for update.", __FILE__, __LINE__);
                echo json_encode(['success' => false, 'message' => 'Table name or primary key is missing.']);
                exit;
            }

            if (empty($data)) {
                echo json_encode(['success' => false, 'message' => 'No changes were detected for the update.']);
                exit;
            }

            $success_count = 0;
            $error_messages = [];

            foreach ($data as $row) {
                try {
                    if (isset($row['pk_value']) && isset($row['changes'])) {
                        $pk_value = clean_input($row['pk_value']);
                        $changes = $row['changes'];

                        $set_parts = [];
                        foreach ($changes as $col => $val) {
                            $sanitized_col = clean_input($col);
                            $sanitized_val = clean_input($val);
                            // Using prepared statements is the gold standard for security.
                            // For this dynamic use case, we are still building the query string, but
                            // we are sanitizing all inputs to minimize risk.
                            $set_parts[] = "`" . $sanitized_col . "` = '" . $sanitized_val . "'";
                        }

                        if (!empty($set_parts)) {
                            $set_clause = implode(', ', $set_parts);
                            $update_query = "UPDATE `$table_name` SET $set_clause WHERE `" . clean_input($primary_key) . "` = '$pk_value'";

                            if ($mysqli->query($update_query) === TRUE) {
                                $success_count++;
                            } else {
                                // This should be caught by the exception handler, but is kept as a failsafe
                                throw new mysqli_sql_exception("Update query returned false.");
                            }
                        }
                    }
                } catch (mysqli_sql_exception $e) {
                    // Log the update error and the specific record that failed
                    log_error("Update failed for record with PK '$pk_value' on table '$table_name'. Error: " . $e->getMessage(), __FILE__, __LINE__);
                    $error_messages[] = "An error occurred while updating the record with " . htmlspecialchars($primary_key) . " = " . htmlspecialchars($pk_value) . ".";
                }
            }

            if (empty($error_messages)) {
                echo json_encode(['success' => true, 'message' => "Successfully updated " . htmlspecialchars($success_count) . " records."]);
            } else {
                echo json_encode(['success' => false, 'message' => implode('<br>', $error_messages)]);
            }
            exit;
        }

        // Action 4: Export data to a CSV file
        if ($action === 'export_csv') {
            $query = isset($_POST['query']) ? trim($_POST['query']) : '';
            $table_name = isset($_POST['table']) ? clean_input($_POST['table']) : 'data';

            if (empty($query)) {
                die("The query is empty. Please enter a valid query to export data.");
            }

            try {
                $result = $mysqli->query($query);
                if ($result === false) {
                    throw new mysqli_sql_exception("Export query returned false.");
                }

                // Set headers for CSV download
                // NEW: Use the new filename format
                $timestamp = date('Y_m_d_H_i_s');
                $filename = $table_name . '_' . $timestamp . '.csv';
                header('Content-Type: text/csv; charset=utf-8');
                header('Content-Disposition: attachment; filename="' . $filename . '"');

                $output = fopen('php://output', 'w');

                // Get column headers and write to CSV
                $field_info = $result->fetch_fields();
                $headers = [];
                foreach ($field_info as $val) {
                    $headers[] = $val->name;
                }
                fputcsv($output, $headers);

                // Write data rows to CSV
                while ($row = $result->fetch_assoc()) {
                    fputcsv($output, $row);
                }

                fclose($output);
                $result->free();
            } catch (mysqli_sql_exception $e) {
                log_error("CSV export failed for query '$query'. Error: " . $e->getMessage(), __FILE__, __LINE__);
                die("An error occurred during the CSV export process. Please check the logs.");
            }
            exit;
        }

        // Action 5: New Action to get unique values for a column
        // This action is now unused but kept in case a future feature needs it.
        // It's no longer used for the textboxes.
        if ($action === 'get_column_values') {
            $table_name = isset($_POST['table']) ? clean_input($_POST['table']) : '';
            $column_name = isset($_POST['column']) ? clean_input($_POST['column']) : '';

            if (empty($table_name) || empty($column_name)) {
                die(json_encode(['error' => 'Table name or column name is missing.']));
            }

            $response = ['values' => []];

            try {
                // Fetch unique values from the column, sorted by value
                $query = "SELECT DISTINCT `" . $column_name . "` FROM `" . $table_name . "` ORDER BY `" . $column_name . "`";
                $result = $mysqli->query($query);
                if ($result) {
                    while ($row = $result->fetch_row()) {
                        $response['values'][] = $row[0];
                    }
                    $result->free();
                }

                header('Content-Type: application/json');
                echo json_encode($response);
            } catch (mysqli_sql_exception $e) {
                log_error("Failed to get unique values for column '$column_name' on table '$table_name': " . $e->getMessage(), __FILE__, __LINE__);
                die(json_encode(['error' => 'An error occurred while fetching column values.']));
            }
            exit;
        }
    }
} catch (Exception $e) {
    // Catch-all for any uncaught exceptions
    log_error("An unexpected error occurred: " . $e->getMessage(), __FILE__, __LINE__);
    // Provide a generic, polite message to the user
    die("An unexpected error occurred on the server. We apologize for the inconvenience.");
}

// --- Main PHP logic for initial page load ---
// Get the list of all tables in the database
$tables = [];
try {
    $result = $mysqli->query("SHOW TABLES FROM `$db_name`");
    if ($result) {
        while ($row = $result->fetch_row()) {
            $tables[] = $row[0];
        }
        $result->free();
        sort($tables);
    }
} catch (mysqli_sql_exception $e) {
    log_error("Failed to list tables: " . $e->getMessage(), __FILE__, __LINE__);
    // Continue with an empty array of tables, the frontend will display an error message
}

// Close the connection
$mysqli->close();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Explorer & Editor</title>
    <!-- Minimal Bootstrap CSS for styling -->
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body {
            background-color: #f8f9fa;
        }
        .container {
            max-width: 1200px;
        }
        .card {
            border: 1px solid #dee2e6;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
        }
        .table td[contenteditable="true"] {
            cursor: pointer;
            background-color: #fffaf0;
        }
        .table td:focus {
            outline: 2px solid #0d6efd;
            background-color: #fff;
        }
        /* Style for the floating Go to Top button */
        #go-to-top-btn {
            position: fixed;
            bottom: 20px;
            right: 20px;
            display: none; /* Hidden by default */
            z-index: 99;
            border: none;
            outline: none;
            background-color: #0d6efd;
            color: white;
            cursor: pointer;
            padding: 15px;
            border-radius: 50%;
            font-size: 18px;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
            transition: opacity 0.3s;
        }

        #go-to-top-btn:hover {
            opacity: 0.8;
        }
    </style>
</head>
<body>
    <div class="container my-5">
        <h1 class="text-center mb-4">Database Explorer & Editor</h1>

        <div class="card p-4">
            <!-- Table Selection -->
            <div class="mb-4">
                <label for="tableSelect" class="form-label fw-bold">1. Please select a table:</label>
                <select id="tableSelect" class="form-select">
                    <option value="" disabled selected>Choose a table</option>
                    <?php
                    foreach ($tables as $table) {
                        echo '<option value="' . htmlspecialchars($table) . '">' . htmlspecialchars($table) . '</option>';
                    }
                    ?>
                </select>
            </div>

            <div id="tableInfo" class="mb-4">
                <!-- Primary Key and Auto-Increment Info will be displayed here -->
            </div>

            <!-- Query Box -->
            <div id="queryContainer" class="mb-4 d-none">
                <label for="queryBox" class="form-label fw-bold">2. Your Query:</label>
                <textarea id="queryBox" class="form-control" rows="3"></textarea>
            </div>

            <!-- Action Buttons -->
            <div id="buttonContainer" class="mb-4 d-none">
                <button id="executeQueryBtn" class="btn btn-primary me-2">Execute Query</button>
                <button id="exportCsvBtn" class="btn btn-success">Export to CSV</button>
            </div>
        </div>

        <!-- Query Result and Update Section -->
        <div class="mt-4">
            <h2 class="text-center mb-3">Query Results</h2>
            <div id="resultContainer">
                <!-- Query results will be displayed here -->
            </div>
            <div id="messageBox" class="mt-3">
                <!-- Status messages (e.g., success/error) will be displayed here -->
            </div>
        </div>

        <!-- Floating Go to Top button -->
        <button id="go-to-top-btn" title="Go to top">&#8679;</button>

        <!-- Hidden form for CSV export -->
        <form id="exportForm" method="POST" action="" class="d-none">
            <input type="hidden" name="action" value="export_csv">
            <input type="hidden" name="table" id="exportTableName">
            <input type="hidden" name="query" id="exportQuery">
        </form>

    </div>

    <!-- Minimal Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
    <script>
    // --- JavaScript Frontend Logic ---
    document.addEventListener('DOMContentLoaded', function() {
        const tableSelect = document.getElementById('tableSelect');
        const tableInfo = document.getElementById('tableInfo');
        const queryBox = document.getElementById('queryBox');
        const queryContainer = document.getElementById('queryContainer');
        const buttonContainer = document.getElementById('buttonContainer');
        const executeQueryBtn = document.getElementById('executeQueryBtn');
        const exportCsvBtn = document.getElementById('exportCsvBtn');
        const resultContainer = document.getElementById('resultContainer');
        const messageBox = document.getElementById('messageBox');
        const exportForm = document.getElementById('exportForm');
        const exportTableName = document.getElementById('exportTableName');
        const exportQuery = document.getElementById('exportQuery');
        const goToTopBtn = document.getElementById('go-to-top-btn');

        let selectedTable = '';
        let primaryKey = '';
        let autoIncrement = '';

        // Function to show a temporary message
        function showMessage(msg, type) {
            messageBox.innerHTML = `<div class="alert alert-${type}" role="alert">${msg}</div>`;
            setTimeout(() => {
                messageBox.innerHTML = '';
            }, 5000);
        }

        // Event listener for table selection dropdown
        tableSelect.addEventListener('change', function() {
            selectedTable = this.value;
            resultContainer.innerHTML = '';
            messageBox.innerHTML = '';

            if (selectedTable) {
                const formData = new FormData();
                formData.append('action', 'get_columns');
                formData.append('table', selectedTable);

                fetch('', {
                    method: 'POST',
                    body: formData
                })
                .then(response => response.json())
                .then(data => {
                    if (data.error) {
                        showMessage(data.error, 'danger');
                        return;
                    }

                    primaryKey = data.primary_key;
                    autoIncrement = data.auto_increment;
                    let infoHtml = `<p class="mb-1"><strong>Primary Key:</strong> ${primaryKey || 'None'}</p>`;
                    if (autoIncrement) {
                         infoHtml += `<p><strong>Auto-Increment Column:</strong> ${autoIncrement}</p>`;
                    }
                    tableInfo.innerHTML = infoHtml;

                    queryContainer.classList.remove('d-none');
                    buttonContainer.classList.remove('d-none');
                    // Automatically pre-populate the query box with a basic SELECT statement
                    queryBox.value = `SELECT * FROM \`${selectedTable}\` LIMIT 10`;

                })
                .catch(error => {
                    console.error('Error:', error);
                    showMessage('An error occurred while fetching table information.', 'danger');
                });
            } else {
                queryContainer.classList.add('d-none');
                buttonContainer.classList.add('d-none');
                tableInfo.innerHTML = '';
            }
        });

        // Event listener for "Execute Query" button
        executeQueryBtn.addEventListener('click', function() {
            const query = queryBox.value;
            if (!query) {
                showMessage('The query box cannot be empty. Please enter a valid query.', 'warning');
                return;
            }

            const formData = new FormData();
            formData.append('action', 'execute_query');
            formData.append('table', selectedTable);
            formData.append('query', query);
            formData.append('primary_key', primaryKey);

            executeQueryBtn.disabled = true;
            executeQueryBtn.textContent = 'Executing...';

            fetch('', {
                method: 'POST',
                body: formData
            })
            .then(response => response.text())
            .then(html => {
                resultContainer.innerHTML = html;
                executeQueryBtn.disabled = false;
                executeQueryBtn.textContent = 'Execute Query';

                const updateChangesBtn = document.getElementById('updateChangesBtn');
                if (updateChangesBtn) {
                    updateChangesBtn.addEventListener('click', updateRecords);
                }

                const cells = resultContainer.querySelectorAll('td[contenteditable="true"]');
                cells.forEach(cell => {
                    cell.dataset.originalValue = cell.textContent.trim();
                });
            })
            .catch(error => {
                console.error('Error:', error);
                showMessage('An unexpected error occurred while executing the query. Please check the console for more details.', 'danger');
                executeQueryBtn.disabled = false;
                executeQueryBtn.textContent = 'Execute Query';
            });
        });

        // Event listener for "Export to CSV" button
        exportCsvBtn.addEventListener('click', function() {
            const query = queryBox.value;
            if (!query) {
                showMessage('The query box cannot be empty. Please enter a valid query to export.', 'warning');
                return;
            }

            exportTableName.value = selectedTable;
            exportQuery.value = query;
            exportForm.submit();
        });

        // Function to handle updating multiple records
        function updateRecords() {
            const tableRows = resultContainer.querySelectorAll('tbody tr');
            const updates = [];

            if (!primaryKey) {
                showMessage('Cannot update. A primary key was not found for this table.', 'danger');
                return;
            }

            tableRows.forEach(row => {
                const pkValue = row.dataset.pkValue;
                if (!pkValue) return;

                const changes = {};
                const cells = row.querySelectorAll('td[contenteditable="true"]');
                cells.forEach(cell => {
                    const colName = cell.dataset.colName;
                    const currentValue = cell.textContent.trim();
                    const originalValue = cell.dataset.originalValue;

                    if (currentValue !== originalValue) {
                        changes[colName] = currentValue;
                    }
                });

                if (Object.keys(changes).length > 0) {
                    updates.push({
                        pk_value: pkValue,
                        changes: changes
                    });
                }
            });

            if (updates.length === 0) {
                showMessage('No changes were detected in the table. Nothing to save.', 'info');
                return;
            }

            const formData = new FormData();
            formData.append('action', 'update_records');
            formData.append('table', selectedTable);
            formData.append('primary_key', primaryKey);
            formData.append('data', JSON.stringify(updates));

            const updateBtn = document.getElementById('updateChangesBtn');
            updateBtn.disabled = true;
            updateBtn.textContent = 'Updating...';

            fetch('', {
                method: 'POST',
                body: formData
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    showMessage(data.message, 'success');
                    const cells = resultContainer.querySelectorAll('td[contenteditable="true"]');
                    cells.forEach(cell => {
                        cell.dataset.originalValue = cell.textContent.trim();
                    });

                } else {
                    showMessage(data.message, 'danger');
                }
                updateBtn.disabled = false;
                updateBtn.textContent = 'Save All Changes';
            })
            .catch(error => {
                console.error('Error:', error);
                showMessage('An unexpected error occurred during the update process. Please check the console for more details.', 'danger');
                updateBtn.disabled = false;
                updateBtn.textContent = 'Save All Changes';
            });
        }

        // --- Go to Top button functionality ---
        window.onscroll = function() {
            if (document.body.scrollTop > 20 || document.documentElement.scrollTop > 20) {
                goToTopBtn.style.display = "block";
            } else {
                goToTopBtn.style.display = "none";
            }
        };

        goToTopBtn.addEventListener('click', function() {
            document.body.scrollTop = 0; // For Safari
            document.documentElement.scrollTop = 0; // For Chrome, Firefox, IE and Opera
        });
    });
    </script>
</body>
</html>