Skip to content
Advertisement

SQL statement stores all results in first item of array

I am running a select statement in which it brings back multiple items of the field ‘task_name’ from records that contain a matching ‘username’ field to a PHP variable above called ‘$user_log’.

$sql_task = "SELECT task_name FROM task_log WHERE username = '$user_log' ORDER BY task_id ";
$task_result = mysqli_query($link, $sql_task);

while ($row = mysqli_fetch_array($task_result)) {
print_r($row);}

Now the statement works and brings back the correct data however it brings all the items back and stores them in the first slot of an array, instead of each item being in separate slots.

Using print_r, I can see the contents of the array. Using just the echo, it displays them all as a single string with no spaces between.

When run with print_r enter image description here

task_log table enter image description here

Would want to call all of the matt_smith ‘task_name’ data items from the table. And be able to reference each one.

It would be great if anyone could help me get each item in a separate slot in the array, or even just a way to call the individual items (id would not work since they don’t have uniform id’s; other users records are in between).

Advertisement

Answer

You need to save each item in an array and then use that array in a way you want. Here is what you need to update:

$sql_task = "SELECT task_name FROM task_log WHERE username = '$user_log' ORDER BY task_id ";
$task_result = mysqli_query($link, $sql_task);

$tasks = array(); // array to store all the task names
while ($row = mysqli_fetch_assoc($task_result)) {
     $tasks[] = $row['task_name'];
}
print_r($tasks); // to print array items

To access array items, you can simply use echo $tasks[0];, echo $tasks[1];, and so on…

Note:

mysqli_fetch_array() has second argument $resulttype and options are:

MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

Meanings:

MYSQLI_ASSOC: Fetch an associative array

MYSQLI_NUM: Fetch a numeric array

MYSQLI_BOTH: Fetch both associative and numeric array.

MYSQLI_BOTH is the default. If we do not provide the second parameter, MYSQLI_BOTH will be considered.

So, you can either any of following three to avoid doubling up of values in the response. mysqli_fetch_array(MYSQLI_ASSOC) OR mysqli_fetch_array(MYSQLI_NUM) OR mysqli_fetch_assoc($task_result)

Also, code readability wise, using associative array i.e. $row['task_name'] is easier to understand than $row[0] (numeric array)

Hope this helps!

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement