PHP MYSQL $row[$variable]
One of the most persistent misconceptions in legacy PHP development involves how array keys are indexed versus how variables are referenced. A common pattern seen in older codebases looks like this:
$row = mysql_fetch_array($result);
echo $row[$col_name];
Developers often expect this syntax to work because it feels intuitive: "I have a variable named $col_name, so I should be able to use it inside the brackets." However, PHP does not interpret [] as a variable lookup in the way C or JavaScript might. The expression $row[$col_name] is parsed specifically as an array access where the key must be a literal string or integer at parse time, not a runtime variable holding a string.
This distinction becomes critical when dealing with dynamic table creation and schema introspection. In your specific scenario, you are querying information_schema.columns to retrieve column names dynamically, storing that value in $col_name, and then attempting to fetch that specific column from a user-defined table ($tabname). The logic seems sound at a high level, but the implementation fails because of how PHP handles variable variables within array syntax.
Variable Variables and Array Syntax
When you write $row[$col_name], PHP is looking for an array key that literally matches the content of $col_name. If $col_name contains the string "username", PHP attempts to access $row["username"]. This actually works in many cases because mysql_fetch_array() returns a numerically indexed result by default (or both, depending on flags).
However, the error you are likely encountering stems from how mysql_fetch_array() behaves when returning associative arrays. If your query selects only one column without an alias, the resulting array might be numeric: [0 => 'value']. In this case, $row[$col_name] tries to access index "username" (a string key), which doesn't exist. You would need to use $row[0].
If your query includes SELECT *, you get an associative array where keys are the column names. But here is the catch: if you want to dynamically select a specific column name stored in a variable, you must ensure that the resulting array is associative. If you run SELECT username FROM table WHERE ..., the result is [0 => 'value']. To make it associative, you need SELECT username AS username FROM table... (redundant but safe) or simply rely on the fact that mysql_fetch_array() returns both if you don't specify flags.
The real issue in your loop is likely that you are trying to access the data using a variable key against an array that might be numerically indexed, or you are confusing string variables with array keys.
The Correct Approach for Dynamic Columns
To reliably fetch data when the column name is determined at runtime, you must ensure two things:
- The SQL query returns the data in an associative format (keys match column names).
- You access the array using a variable that holds the string key.
If mysql_fetch_array() is used without specifying $MYSQL_ASSOC or $MYSQL_BOTH, it defaults to returning both numeric and associative indices. However, if you switch to mysqli or PDO, you have more control. Here is how you should structure the dynamic fetch logic:
// Fetch column info first
$resultSchema = mysql_query("SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = '$tabname'");
while($rowSchema = mysql_fetch_array($resultSchema)) {
$col_name = $rowSchema['COLUMN_NAME']; // Get the name as a string
// Construct dynamic query
$myQuery = "SELECT `".$col_name."` FROM ".$tabname." WHERE sampleid='".$sid."'";
// Execute query
$resultData = mysql_query($myQuery);
if ($rowData = mysql_fetch_array($resultData, MYSQL_ASSOC)) {
// Use variable directly as key because array is associative
echo $rowData[$col_name];
} else {
echo "No data found for column: ".$col_name;
}
}
Notice the use of MYSQL_ASSOC in mysql_fetch_array(). This forces the function to return an array where keys are the column names (e.g., 'username' => 'john'). Without this, you get numeric indices (0 => 'john'), and accessing $rowData[$col_name] would fail because there is no key named 'username', only index 0.
Modernization Warning
While fixing the array access logic resolves your immediate issue, you must address the underlying library. The mysql_* extension was deprecated in PHP 5.5 and removed entirely in PHP 7.0. Attempting to run this code on a modern server will result in a fatal error regardless of how correct the array syntax is.
You should migrate to mysqli or PDO. These extensions support prepared statements, which are essential when dealing with dynamic table names or column names to prevent SQL injection vulnerabilities. Although you cannot parameterize table names (they must be whitelisted), using PDO offers a cleaner interface for introspection:
$stmt = $pdo->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$tabname'");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$col_name = $row['COLUMN_NAME'];
// Whitelist the table name to prevent injection
if (in_array($table, $allowed_tables)) {
$stmtData = $pdo->prepare("SELECT `".$col_name."` FROM ".$table." WHERE sampleid = :sid");
$stmtData->execute([':sid' => $sid]);
$data = $stmtData->fetch(PDO::FETCH_ASSOC);
if ($data) {
echo $data[$col_name];
}
}
}
Summary of Pitfalls
The primary pitfall here is assuming that [] behaves like a variable reference in all languages. In PHP, $array[$var] requires the array to have a key matching the string value inside $var. If your array is numerically indexed, this fails. Always verify whether mysql_fetch_array() (or its modern equivalents) returns associative keys for your specific query structure. Additionally, never rely on dynamic table or column names without strict validation against a whitelist; using information_schema allows any user to inject arbitrary SQL if not handled carefully.
For new projects, consider checking out the resources at https://laravelcompany.com for insights into modern PHP architecture patterns that avoid these legacy pitfalls entirely.