PHP+MySQL记录状态判断异常：值为0却显示1如何解决？

Let’s break down why your code is incorrectly showing a status of 1 when the actual value is 0, and fix it step by step.

First, Debug the Actual Value #

The first thing you should do is uncomment your var_dump($row[0]); line. This will tell you exactly what value PHP is receiving from MySQL—sometimes the issue isn’t with your logic, but with the data itself (like hidden spaces, string vs integer mismatches, or unexpected results).

Common Issues & Fixes #

1. Multiple Records Matching the Barcode

If your barcode field isn’t set to unique, your query might return multiple rows, and fetch_array() only grabs the first one. That first row could have a status of 1 even if another matching row has 0.

Fix: Add LIMIT 1 to your query to ensure you only get one record:

$query = "SELECT statuswm FROM users WHERE barcode='D189404954' LIMIT 1";

2. Type Mismatch or Hidden Characters

If your statuswm field is a string type (like VARCHAR instead of INT), it might store values like "0 " (with trailing spaces) or "0" as a string. Loose comparison (==) can lead to unexpected type conversions, but more often, hidden characters make the value not equal to 0 as expected.

Fix: Convert the value to an integer explicitly, and use strict comparison (===) to avoid type confusion:

// Convert to integer to handle string values or hidden spaces
$status = (int)$row[0];

if ($status === 1) { 
    echo $status; 
} else { 
    echo "not in stock"; 
}

3. Index-Based Fetching Is Error-Prone

Using MYSQLI_NUM means you’re relying on the order of columns in your query. If you ever modify the query to include more columns, this index could break. A safer approach is to use associative arrays.

Fix: Use MYSQLI_ASSOC to reference the column by name:

$row = $result->fetch_array(MYSQLI_ASSOC);
$status = (int)$row['statuswm']; // Clearer and less likely to break

Modified Working Code #

Here’s the full adjusted code with debugging and fixes included:

<?php 
$link = mysqli_connect("localhost", "root", "", "watermeter"); 
// Check connection 
if($link === false){ 
    die("ERROR: Could not connect. " . mysqli_connect_error()); 
} 

$query = "SELECT statuswm FROM users WHERE barcode='D189404954' LIMIT 1";
$result = mysqli_query($link, $query); 

// Debug: See exactly what value we're getting
$row = $result->fetch_array(MYSQLI_ASSOC);
var_dump($row['statuswm']); 

$status = (int)$row['statuswm'];

if ($status === 1) { 
    echo $status; 
} else { 
    echo "not in stock"; 
} 

// Close connection 
mysqli_close($link); 
?>

Final Notes #

• Check your database schema: Make sure statuswm is an INT type if you’re storing numeric values (this avoids most string-related issues).
• Add a unique constraint to the barcode field to prevent duplicate records from causing unexpected results.

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