SQL查询首行重复返回问题求助（附代码与对比截图）

Hey Martin, let's dig into why your query is spitting out the first row twice—since you've confirmed the same SQL works fine in phpMyAdmin, the issue is almost certainly in how your PHP code is handling the result set. Here are the most likely culprits and fixes:

1. You're calling num_rows wrong (common mysqli gotcha!) #

Looking at your code snippet, you wrote $stmt->num_rows()—but num_rows is a property, not a method in mysqli_stmt. Calling it with parentheses will throw an error, and if error reporting is turned off in your environment, this could mess up the flow of your script and lead to unexpected behavior like duplicate rows.

Fix this immediately by removing the parentheses:

echo "numrows: " . $stmt->num_rows; // No parentheses!

2. Accidental extra fetch() call before your loop #

If you're manually calling $stmt->fetch() once before starting your output loop, you might be accidentally rendering that first row twice (or causing loop logic to misbehave). For example, a do-while loop that runs before checking the fetch result can lead to duplication:

// Bad: do-while runs once before fetching data
$stmt->bind_result(...);
do {
    echo "<div>$title</div>"; // Outputs stale data first, then duplicates if you fetched earlier
} while($stmt->fetch());

Stick to a standard while loop to avoid this:

// Correct loop structure
while($stmt->fetch()) {
    echo "<p>ID: $id, Title: $title</p>";
}

3. Your output code is rendering the first row twice #

Sometimes the duplicate isn't from the result set itself, but your HTML/PHP is printing the first row manually AND then including it in the loop. For example, if you have code that checks for rows, prints the first row, then loops through all rows (including that first one again).

To test this, capture all rows into an array first and inspect the raw data:

$allRows = [];
while($stmt->fetch()) {
    $allRows[] = [
        'id' => $id,
        'title' => $title,
        'content' => $content,
        'posted' => $posted,
        'author' => $author,
        'login' => $login
    ];
}
// Print the array to see if the duplicate exists here
print_r($allRows);

If print_r shows no duplicate first row, the problem is definitely in how you're rendering the data, not the query.

Full Corrected Code Example #

Here's your code with the key fixes applied:

if($stmt = $conn->prepare("SELECT a.id, a.title, a.content, a.posted, a.author, u.login FROM articles a INNER JOIN users u ON a.author=u.id ORDER BY a.id DESC")) {
    $stmt->execute();
    $stmt->bind_result($id,$title,$content,$posted,$author,$login);
    $stmt->store_result();
    
    // Fixed num_rows call
    echo "numrows: " . $stmt->num_rows;
    
    // Clean loop to fetch and output all rows
    while($stmt->fetch()) {
        // Replace this with your actual output logic
        echo "<article>
                <h2>$title</h2>
                <p>By $login on $posted</p>
                <div>$content</div>
              </article>";
    }
    
    $stmt->close();
}

Start with fixing the num_rows mistake—it's the most likely cause here. If that doesn't resolve it, use the array capture trick to narrow down whether the issue is in the result set or your output.

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