PHP / MySQL：使用MySQL Query中的数组元素声明变量

Question

I am trying to retrieve data from my database and assign the array elements to PHP variables that I wish to use throughout my program so that I don't have to worry about posting separate form data later on.

I know the query I currently have set up is currently working from previous tests but when I try to use a FOR loop to output variables I've assigned from the MySQL query result, only the first element from the database is displayed.

PHP/MySQL Code:

// Retrieve tasks from the tasks table 
$tasks_sql = "SELECT DISTINCT taskname FROM tasks";
$tasks_result = mysqli_query($usermysqli, $tasks_sql);
if (! $tasks_result){
      die('Could not retrieve data: ' . mysql.error());
    }

// Array for assigning tasknames to global variables

while($tasks_displayed = $tasks_result->fetch_array()) {

for ($i=0, $inputlen = count($tasks_displayed); $i < $inputlen; $i++) {
${'line'.($i+1)} = $tasks_displayed[$i];
}

}   

// Test to see if the array above actually worked.
echo "Task 1 from array: " . $line1 . "<br>";
echo "Task 2 from array: " . $line2 . "<br>";
echo "Task 3 from array: " . $line3 . "<br>";
echo "Task 4 from array: " . $line4 . "<br>";
echo "Task 5 from array: " . $line5 . "<br>";
echo "Task 6 from array: " . $line6 . "<br>";

Only the first element is displayed properly and variables $line2 - $line6 are currently null. Is this a simple coding error?

Answer 1

Since your for loop is nested inside your while fetch loop and you are using the for loop to populate individual variables, on each loop iteration you are overwriting the same variable. In other words, on every fetch, you get back an array of 1 row having 1 element. The for loop then goes over that single row and populates only the first variable, again and again.

The for loop needs to happen after the while loop, once all rows have been fetched into an array.

// Fetch an associative array
// array to hold all tasks
$tasks = array();
// Also, consider sticking with the procedural method since that's what you use elsewhere
while ($tasks_displayed = $tasks_result->fetch_assoc()) {
// procedural alternate:
// while ($tasks_displayed = mysqli_fetch_assoc($tasks_result)) {
  // Append the task onto your array
  $tasks[] = $tasks_displayed['taskname'];
}
// Following the loop you now have an array of tasks.
// you can use a for loop to display them:
$tasklength = count($tasks);
for ($i = 0; $i < $tasks; $i++) {
  // Populate your variables...
  ${'line'.($i+1)} = $tasks[$i];
}

But...

That said, an incrementing for loop is really uncommonly used in PHP. foreach is almost always p preferred. If you really must populate these local variables instead of just using them directly from the $tasks array we populated earlier, use a foreach:

foreach ($tasks as $key => $task) {
    ${'line'.($key + 1)} = $tasks[$key];
}

Really, the most conventional thing to do here would just be to keep the tasks in the $tasks array created inside the while loop instead of populating a potentially unknown number of variables, adding them to the local or global symbol table unnecessarily. If the tasks are related (they arguably are, simply since they are all tasks), then it is wise to keep them bound together in an array.

// Use $tasks as an array, don't populate variables:
foreach ($tasks as $key => $task) {
  echo "Task $key from array: $task<br/>
"; 
}

As noted in comments, watch out for mixing up mysql_*() with the mysqli_*() APIs. Your call to mysql_error() should be instead a call to mysqli_error($usermysqli)