CODEWITHSUNDEEP
phpmysql
Jeff P.
Jeff P.

Reputation: 3004

PHP/MySQL: Declare variables using array elements from MySQL Query

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?

Upvotes: 2

Views: 1916

Answers (3)

Cleric
Cleric

Reputation: 3193

This is not the answer to your question, but instead another approach to the problem you outlined

// 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


$tasks_displayed = $tasks_result->fetch_array();

foreach( $tasks_displayed as $key => $value )
{
    echo "Task ".($key + 1)." from array: " . $value . "<br>";
}

Upvotes: 1

adey_888
adey_888

Reputation: 316

Store the tasks as an array. Try this code - is untested and warranty free but quite straightforward.

$tasks = [];  // array to store tasks

while($tasks_displayed = $tasks_result->fetch_array()) {  // could use fetch_row instead?

    $tasks[] = $tasks_displayed[0];

}

var_dump($tasks);

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270677

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/>\n"; 
}

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)

Upvotes: 3

Related Questions