PHP+MySQL多列条件判断：基于任务状态渲染HTML表格

Got it, let's break this down step by step to build your dynamic task table. We'll cover secure database connection, fetching task statuses, and rendering the table based on your MySQL data.

1. Set Up a Secure MySQL Connection #

First, we'll connect to your database using MySQLi (object-oriented style) – this supports prepared statements to keep your code safe from SQL injection.

<?php
// Database credentials (use environment variables in production, not hardcoding!)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

// Create connection
$conn = new mysqli($host, $username, $password, $dbname);

// Check for connection errors
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2. Fetch Task Statuses from table1 #

Next, we'll pull the task status values from your table. I'll assume you're fetching a single record (e.g., for a specific user/project) – adjust the query if you need to handle multiple records.

<?php
// Prepare query with a placeholder (use prepared statements for dynamic values!)
$sql = "SELECT task1, task2, task3 FROM table1 WHERE id = ?"; // Replace 'id' with your primary key
$stmt = $conn->prepare($sql);

// Bind your record ID (replace $recordId with your actual value, e.g., from user session)
$recordId = 1;
$stmt->bind_param("i", $recordId);

// Execute and get results
$stmt->execute();
$result = $stmt->get_result();
$taskStatuses = $result->fetch_assoc();

// Clean up statement
$stmt->close();
?>

3. Dynamically Render the HTML Table #

Now we'll generate the table rows based on the task statuses. We'll map each task column to a table row, and adjust the progress badge based on whether the task is completed (1) or not (0):

<table class="table table-striped">
    <tr>
        <th style="width: 10px">#</th>
        <th>Task</th>
        <th>Progress</th>
    </tr>
    <?php
    // Map task columns to user-friendly names for cleaner looping
    $tasks = [
        1 => ["name" => "Task 1", "status" => $taskStatuses['task1']],
        2 => ["name" => "Task 2", "status" => $taskStatuses['task2']],
        3 => ["name" => "Task 3", "status" => $taskStatuses['task3']]
    ];

    foreach ($tasks as $id => $task) {
        // Determine which badge to show based on status
        $progressBadge = match($task['status']) {
            1 => '<span class="badge bg-green">Completed</span>',
            0 => '<span class="badge bg-yellow">In Progress</span>',
            default => '<span class="badge bg-red">Error - Please Check</span>'
        };
        ?>
        <tr>
            <td><?php echo $id; ?>.</td>
            <td><?php echo htmlspecialchars($task['name']); ?></td>
            <td><?php echo $progressBadge; ?></td>
        </tr>
        <?php
    }
    ?>
</table>

<?php
// Close database connection
$conn->close();
?>

Important Best Practices #

• Security: Always use prepared statements for database queries to avoid SQL injection. Use htmlspecialchars() when outputting dynamic content to prevent XSS attacks.
• Production Prep: Store database credentials in environment variables (never hardcode them), add error handling for query failures, and consider using PDO instead of MySQLi if you need database flexibility.
• Scalability: If you ever add more tasks, just update the $tasks array instead of rewriting the loop.

内容的提问来源于stack exchange，提问作者Ryan