如何通过HTML输入按钮更新MySQL数据库的用户练习列

Alright, let's break this down step by step—you need to update your MySQL user table via HTML buttons, while respecting the ex1 → ex2 access restriction and using jQuery for validation. Here's how to make it work smoothly, with security in mind:

First, we'll build the UI with buttons to update progress, and use jQuery to handle clicks, validation, and AJAX requests to the backend. We'll also dynamically show/hide the ex2 section based on the user's ex1 progress.

<!-- Hidden field to store logged-in user ID (replace with your auth method, e.g., session data) -->
<input type="hidden" id="user-id" value="1">

<!-- Exercise 1 controls -->
<button class="update-progress" data-exercise="ex1" data-target="1">完成练习1第1题</button>
<button class="update-progress" data-exercise="ex1" data-target="4">完成练习1第4题</button>
<button class="update-progress" data-exercise="ex1" data-target="10">完成练习1全部题目</button>
<div class="status">练习1进度: <span id="ex1-value">0</span>/10</div>

<!-- Exercise 2 controls (hidden by default) -->
<div id="ex2-container" style="display: none; margin-top: 1rem;">
  <button class="update-progress" data-exercise="ex2" data-target="1">完成练习2第1题</button>
  <button class="update-progress" data-exercise="ex2" data-target="10">完成练习2全部题目</button>
  <div class="status">练习2进度: <span id="ex2-value">0</span>/10</div>
</div>

Now the jQuery logic to handle interactions:

$(document).ready(function() {
  const userId = $('#user-id').val();
  // Load initial progress on page load
  loadUserProgress(userId);

  // Handle progress update button clicks
  $('.update-progress').click(function() {
    const exercise = $(this).data('exercise');
    const targetValue = parseInt($(this).data('target'));
    const currentValue = parseInt($(`#${exercise}-value`).text());

    // Frontend validation: don't allow regressing or exceeding 10
    if (targetValue > 10 || targetValue <= currentValue) {
      alert('这个操作无效哦！');
      return;
    }

    // Enforce ex2 access rule: only allow if ex1 is 10
    if (exercise === 'ex2' && parseInt($('#ex1-value').text()) !== 10) {
      alert('请先完成练习1的全部题目，才能开始练习2！');
      return;
    }

    // Send AJAX request to update database
    $.ajax({
      url: 'update_progress.php',
      method: 'POST',
      data: {
        user_id: userId,
        exercise: exercise,
        target_value: targetValue
      },
      success: function(response) {
        const result = JSON.parse(response);
        if (result.success) {
          // Update UI with new progress
          $(`#${exercise}-value`).text(result.new_value);
          // Show ex2 section if ex1 just hit 10
          if (exercise === 'ex1' && result.new_value === 10) {
            $('#ex2-container').show();
          }
        } else {
          alert('更新失败：' + result.message);
        }
      },
      error: function() {
        alert('网络出错了，请稍后重试');
      }
    });
  });

  // Fetch current user progress from backend
  function loadUserProgress(userId) {
    $.ajax({
      url: 'get_progress.php',
      method: 'GET',
      data: { user_id: userId },
      success: function(response) {
        const progress = JSON.parse(response);
        $('#ex1-value').text(progress.ex1);
        $('#ex2-value').text(progress.ex2);
        // Show ex2 section if user already finished ex1
        if (progress.ex1 === 10) {
          $('#ex2-container').show();
        }
      }
    });
  }
});

We'll create two PHP scripts: one to fetch current progress, and another to update it. Critical note: never rely solely on frontend validation—always enforce rules on the backend too.

get_progress.php (Fetch user progress) #

<?php
// Connect to your MySQL database (replace with your credentials)
$conn = mysqli_connect('localhost', 'your_username', 'your_password', 'your_database');
if (!$conn) {
  die(json_encode(['success' => false, 'message' => '数据库连接失败']));
}

// Validate user ID (use session auth in production instead of GET parameter!)
session_start();
$userId = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : $_GET['user_id'];
if (!is_numeric($userId)) {
  die(json_encode(['success' => false, 'message' => '无效用户']));
}

// Use prepared statements to prevent SQL injection
$stmt = $conn->prepare("SELECT ex1, ex2 FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

if ($user) {
  echo json_encode([
    'ex1' => (int)$user['ex1'],
    'ex2' => (int)$user['ex2']
  ]);
} else {
  echo json_encode(['success' => false, 'message' => '用户不存在']);
}

$stmt->close();
$conn->close();
?>

update_progress.php (Update progress in database) #

<?php
session_start();
$conn = mysqli_connect('localhost', 'your_username', 'your_password', 'your_database');
if (!$conn) {
  die(json_encode(['success' => false, 'message' => '数据库连接失败']));
}

// Validate incoming data
if (!isset($_POST['user_id'], $_POST['exercise'], $_POST['target_value'])) {
  die(json_encode(['success' => false, 'message' => '参数缺失']));
}

$userId = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : $_POST['user_id'];
$exercise = $_POST['exercise'];
$targetValue = (int)$_POST['target_value'];

// Validate exercise field name (prevent SQL injection via field name)
if (!in_array($exercise, ['ex1', 'ex2'])) {
  die(json_encode(['success' => false, 'message' => '无效的练习字段']));
}

// Enforce ex2 access rule on backend
if ($exercise === 'ex2') {
  $stmt = $conn->prepare("SELECT ex1 FROM users WHERE id = ?");
  $stmt->bind_param("i", $userId);
  $stmt->execute();
  $result = $stmt->get_result();
  $user = $result->fetch_assoc();
  if ($user['ex1'] != 10) {
    die(json_encode(['success' => false, 'message' => '权限不足，请先完成练习1']));
  }
  $stmt->close();
}

// Update progress, ensuring value doesn't exceed 10
$stmt = $conn->prepare("UPDATE users SET $exercise = LEAST(?, 10) WHERE id = ?");
$stmt->bind_param("ii", $targetValue, $userId);

if ($stmt->execute()) {
  // Fetch updated value to return to frontend
  $stmt = $conn->prepare("SELECT $exercise FROM users WHERE id = ?");
  $stmt->bind_param("i", $userId);
  $stmt->execute();
  $result = $stmt->get_result();
  $newValue = $result->fetch_assoc()[$exercise];
  echo json_encode(['success' => true, 'new_value' => (int)$newValue]);
} else {
  echo json_encode(['success' => false, 'message' => '更新失败']);
}

$stmt->close();
$conn->close();
?>

• Use proper user authentication: Replace the hidden user-id field with session or JWT authentication in production—never trust user input for identifying who's making the request.
• Validate everything: Both frontend and backend should validate inputs (e.g., target values can't exceed 10, exercise names are valid).
• Prevent SQL injection: Always use prepared statements for database queries, especially when using user-provided values in field names or conditions.
• Avoid overwriting progress: The LEAST() function in the MySQL query ensures we never set a value higher than 10, even if a malicious user tries to send an invalid target.

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