Mysqli SELECT查询中affected_rows无法识别含空格玩家名称问题

Hey there, let's break down why your code isn't correctly detecting existing players with spaces in their names—spoiler: it's not the spaces causing the issue, it's the method you're using to check for query results.

The Root Cause #

affected_rows in mysqli is built to count rows modified by INSERT, UPDATE, or DELETE operations. For SELECT queries, this value is unreliable (it might return -1 or an incorrect count depending on your connection setup). To get the number of rows returned by a SELECT statement, you need to use num_rows instead.

Fixed Code #

Here's the adjusted version of your code with the correct result check:

$nick = trim('Your Nightmare'); 
$q = connect_db(); 
// Checking if the player already exists in the database
$conn = $q->prepare("SELECT name FROM players WHERE name = ?"); 
$conn->bind_param("s", $nick); 
$conn->execute(); 
$conn->bind_result($db_nick); 
$conn->store_result(); 

// Replace affected_rows with num_rows for SELECT queries
if($conn->num_rows === 0) { 
    echo 'inserted~!<br/>'; 
} else { 
    echo 'player already exists!'; 
} 

$conn->free_result(); 
$conn->close(); 
$q->close();

Quick Notes #

• You already called store_result() correctly—this step is mandatory when using bind_result() because num_rows needs the result set to be stored in memory to return an accurate count.
• Your parameter binding (bind_param("s", $nick)) works perfectly for strings with spaces—mysqli prepared statements handle special characters and spaces natively, so that wasn't the source of your problem.

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