如何通过索引而非列名访问MySQL查询结果的行数据？

Hey there! Let's break down why you're hitting that undefined offset error and get it fixed up quickly.

The Root Issue #

When you use fetch_assoc(), it spits back an associative array where the keys exactly match your table's column names (like 'cursor_amount' or 'grandma_amount'). There are no numeric indexes (like 0 or 1) in this array—so trying to access $row[0] or $row[1] is like asking for a key that doesn't exist, hence the error.

Simple Fixes to Use Numeric Indexes #

Here are two straightforward ways to get a numeric-indexed result set instead:

1. Switch to fetch_row()
   This method returns a pure numeric array, ordered the same way your columns appear in the query. Perfect for accessing values by position:

   $sql = "SELECT * FROM user_buildings WHERE player_id = '$id'";
   $result = mysqli_query($conn, $sql);
   $amounts = array();
   while ($row = $result->fetch_row()) {
       $amounts[0] = $row[0];
       $amounts[1] = $row[1];
   }
   

2. Use fetch_array() with MYSQLI_NUM
   This lets you explicitly request a numeric array (you can also use MYSQLI_ASSOC for associative, or MYSQLI_BOTH if you need both types):

   $sql = "SELECT * FROM user_buildings WHERE player_id = '$id'";
   $result = mysqli_query($conn, $sql);
   $amounts = array();
   while ($row = $result->fetch_array(MYSQLI_NUM)) {
       $amounts[0] = $row[0];
       $amounts[1] = $row[1];
   }
   

A Smarter Long-Term Practice: Ditch SELECT * #

Using SELECT * is risky—if someone adds or removes a column from your table later, your numeric indexes will shift and break your code. Instead, explicitly list the columns you need. This makes your code predictable and more efficient:

$sql = "SELECT cursor_amount, grandma_amount FROM user_buildings WHERE player_id = '$id'";
$result = mysqli_query($conn, $sql);
$amounts = array();
while ($row = $result->fetch_row()) {
    $amounts[0] = $row[0]; // Directly maps to cursor_amount
    $amounts[1] = $row[1]; // Directly maps to grandma_amount
}

Critical Security Note: Stop SQL Injection! #

Your current code plugs $id directly into the SQL query, which is a huge security hole (hackers can exploit this to steal or modify your data). Always use prepared statements instead:

// Prepare query with a placeholder
$sql = "SELECT cursor_amount, grandma_amount FROM user_buildings WHERE player_id = ?";
$stmt = mysqli_prepare($conn, $sql);

// Bind $id to the placeholder (use "i" for integers, "s" for strings)
mysqli_stmt_bind_param($stmt, "i", $id);

// Execute and get results
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$amounts = array();
while ($row = $result->fetch_row()) {
    $amounts[0] = $row[0];
    $amounts[1] = $row[1];
}

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