PHP中如何获取SQL结果集下一行数据？附答题页优化咨询

Hey there! Great job getting a working single-question quiz up and running during lockdown—super resourceful. Let's tackle your two main needs: adding navigation between questions and optimizing your existing code.

1. Implementing Question Navigation #

Since HTTP is stateless, we'll use PHP sessions to track the current question index. Here's how to modify your code to add "Previous" and "Next" functionality:

Step 1: Update the PHP Logic #

Add session handling and question navigation controls:

<?php
session_start(); // Initialize session to track current question

$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Handle navigation actions (prev/next/reset)
if (isset($_GET['action'])) {
    $totalQuestions = $conn->query("SELECT COUNT(*) FROM quiz_db")->fetch_row()[0];
    $currentIndex = isset($_SESSION['current_question']) ? $_SESSION['current_question'] : 0;

    switch ($_GET['action']) {
        case 'next':
            $_SESSION['current_question'] = min($currentIndex + 1, $totalQuestions - 1);
            break;
        case 'prev':
            $_SESSION['current_question'] = max($currentIndex - 1, 0);
            break;
        case 'reset':
            $_SESSION['current_question'] = 0;
            break;
    }
}

// Get current question index (default to 0 if no session exists)
$currentIndex = isset($_SESSION['current_question']) ? $_SESSION['current_question'] : 0;

// Fetch the current question using LIMIT
$sql = "SELECT * FROM quiz_db LIMIT ?, 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $currentIndex);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// Get total question count for navigation button state
$totalQuestions = $conn->query("SELECT COUNT(*) FROM quiz_db")->fetch_row()[0];
$conn->close();

// Shuffle answers as before
$answerslist = array($row["answercorrect"], $row["answerincorrect1"], $row["answerincorrect2"], $row["answerincorrect3"]);
shuffle($answerslist);
?>

Step 2: Add Navigation Buttons to the Frontend #

Add these buttons above your question/answer section to let users switch questions:

<!-- Navigation Controls -->
<div class="quiz-nav">
    <button onclick="window.location.href='?action=prev'" <?php echo $currentIndex === 0 ? 'disabled' : ''; ?>>上一题</button>
    <span>题目 <?php echo $currentIndex + 1; ?> / <?php echo $totalQuestions; ?></span>
    <button onclick="window.location.href='?action=next'" <?php echo $currentIndex === $totalQuestions - 1 ? 'disabled' : ''; ?>>下一题</button>
    <button onclick="window.location.href='?action=reset'">重新开始</button>
</div>

<!-- Your Existing Question & Answer Buttons -->
<h3><?php echo $row["question"]; ?></h3>
<div class="row">
  <div class="col-sm-3">
    <button class="answer" data-answer="<?php echo $answerslist[0]; ?>"><?php echo $answerslist[0]; ?></button>
  </div>
  <div class="col-sm-3">
    <button class="answer" data-answer="<?php echo $answerslist[1]; ?>"><?php echo $answerslist[1]; ?></button>
  </div>
  <div class="col-sm-3">
    <button class="answer" data-answer="<?php echo $answerslist[2]; ?>"><?php echo $answerslist[2]; ?></button>
  </div>
  <div class="col-sm-3">
    <button class="answer" data-answer="<?php echo $answerslist[3]; ?>"><?php echo $answerslist[3]; ?></button>
  </div>
</div>

<div id="result"></div>

Step 3: Simplify the JavaScript Logic #

Instead of writing 4 separate click functions, use a single event listener for all answer buttons:

// Bind click event to all answer buttons
document.querySelectorAll('.answer').forEach(button => {
  button.addEventListener('click', function() {
    const clickedAnswer = this.getAttribute('data-answer');
    const correctAnswer = "<?php echo $row["answercorrect"]; ?>";
    const resultEl = document.getElementById("result");

    if (clickedAnswer === correctAnswer) {
      resultEl.textContent = "CORRECT!";
      resultEl.className = "result correct";
    } else {
      resultEl.textContent = "WRONG!";
      resultEl.className = "result wrong";
    }
  });
});

Add this CSS to replace inline styles:

.result {
  padding: 10px;
  margin-top: 15px;
  border-radius: 5px;
  color: white;
}
.correct { background-color: #32CD32; }
.wrong { background-color: #CC0000; }
.quiz-nav button:disabled { opacity: 0.5; cursor: not-allowed; }

2. Code Optimization Suggestions #

Here are some tweaks to make your code cleaner, safer, and more maintainable:

• Prevent SQL Injection: Always use prepared statements (like in the updated PHP code above) instead of directly inserting variables into SQL queries.
• Reusable Database Connection: Wrap your database connection logic in a function or class to avoid repeating code across pages.
• Error Handling: Add checks for empty query results (e.g., if $row is null, show a "No questions found" message).
• AJAX for Smooth Navigation: For a better user experience, use AJAX to load new questions without refreshing the entire page. You could create a PHP endpoint that returns question data as JSON, then use fetch() to update the DOM dynamically.
• Session Security: Add session security settings (e.g., session_set_cookie_params() with httponly and secure flags) to prevent session hijacking.
• Separate Concerns: Move CSS to a dedicated .css file, JavaScript to a .js file, and PHP logic to a separate include file if your project grows.

Hope these changes help you expand your quiz and make the code more robust. Let me know if you run into any issues with implementing this!

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