如何在MySQLi中根据行值获取对应的列名？

Hey there! Let's work through how to pull just the column names that match a specific value, instead of dumping all columns and their values like your original code does.

First, a quick heads-up about your initial code: mysqli_fetch_array() uses MYSQLI_BOTH by default, which returns both associative (column names as keys) and indexed (numeric keys) arrays. That means your loop would iterate over each value twice—once with the column name, once with a number. We'll fix that first to avoid confusion.

Here's a working solution tailored to your needs:

// Make sure your database connection is set up correctly first
$mysqli = new mysqli("your-host", "your-username", "your-password", "your-database");

// Define the specific value you want to match against
$targetValue = "your-desired-value-here";

// Run your query (same WHERE clause as your original code)
$result = $mysqli->query("SELECT * FROM `xtdf` WHERE posterid='bike'");

// Check if the query returned any rows
if ($result->num_rows > 0) {
    // Fetch only associative array (column names as keys, no numeric indexes)
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
    
    // Loop through each column-value pair
    foreach ($row as $columnName => $value) {
        // Match the value to your target (use === for strict type matching if needed)
        if ($value == $targetValue) {
            echo "Matching column: " . $columnName . "<br>";
        }
    }
} else {
    echo "No rows found with posterid='bike'";
}

// Clean up the connection when done
$mysqli->close();

Quick Tips to Avoid Common Issues: #

• Strict vs Loose Matching: Use === instead of == if you need to match both value and data type (e.g., preventing "5" from matching the integer 5).
• Multiple Rows: If your query returns more than one row, wrap the fetch and loop in a while loop to check every row:

  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
      foreach ($row as $columnName => $value) {
          if ($value == $targetValue) {
              echo "Matching column in row: " . $columnName . "<br>";
          }
      }
  }
  

• Empty Values: If you're looking for empty strings or NULLs, handle those explicitly (e.g., if ($value === "") or if (is_null($value))).

If your previous attempts returned nothing, common culprits might be:

• Using MYSQLI_NUM (only numeric keys) instead of MYSQLI_ASSOC
• Strict comparison when the stored data type doesn't match your target value
• Your WHERE clause returning no rows (double-check that posterid='bike' exists in your table)

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