Edgar Cooper Edgar Cooper - 3 years ago 206
PHP Question

Request returns Array

Table:

id | date | number
----------------------
1 | 01.01 | 12
2 | 02.01 | 15
3 | 03.01 | 174
4 | 04.01 | 14
5 | 05.01 | 65
6 | 06.01 | 78
7 | 07.01 | 69
8 | 08.01 | 147
9 | 09.01 | 12
10 | 10.01 | 87


I'm trying to output last 7 values from
date
ordered by
id
with some implode.

$day=$mysqli->query("SELECT `date` FROM `online` ORDER BY `id` DESC LIMIT 7");
$day=$day->fetch_all();
$day="'". implode("', '", $day) . "'";
echo $day;


echo
returns
'Array',
x7. How to output
'01.01','02.01', etc.
?

Answer Source

First of all, your code uses $day to store at least three different things during its lifetime. While the compiler doesn't complain, it is confusing and difficult to understand for humans. Let rewrite your code to use different variables for different items:

$result = $mysqli->query("SELECT `date` FROM `online` ORDER BY `id` DESC LIMIT 7");
$days = $result->fetch_all();
$text = "'" . implode("', '", $days) . "'";
echo $text;

A quick print_r($days) will reveal you that the value returned by $result->fetch_all(); is a two-dimensional array. Each of its items is an array, one row from the result set.

There are many ways to get the date column of each row returned by $result->fetch_all(). The way that is the easiest to read and understand is to ask mysqli_result::fetch_all() to return the rows as associative arrays indexed by column names (by default it returns arrays numerically indexed) then use the function array_column() to get only the values of the date column and pass only them to implode().

The code is like this:

$result = $mysqli->query("SELECT `date` FROM `online` ORDER BY `id` DESC LIMIT 7");
$days = $result->fetch_all(MYSQLI_ASSOC);
$text = "'" . implode("', '", array_column($days, 'date')) . "'";
echo $text;

The next step towards readability (with a small performance penalty) and correctness is to quote each day individually before joining them into the final string. The function array_map() can be used for that; it applies a callback function to each item of the passed array and returns a new array that contains the values returned by the callback function.

$result = $mysqli->query("SELECT `date` FROM `online` ORDER BY `id` DESC LIMIT 7");
$days = $result->fetch_all(MYSQLI_ASSOC);
$text = implode(', ',                 // Join the quoted days with commas
    array_map(
        function($day) {
            return "'{$day}'";        // Put each day in single quotes
        },
        array_column($days, 'date')   // Get only the 'date' column
    )
);
echo $text;

A quick test with a query that doesn't return any result (use an empty table or add an impossible WHERE condition) reveals that this code doesn't produce any output (which is correct since there is no data retrieved from the database) while the original code produces an empty pair of apostrophes (''), which is not correct (let's remember the requirement was to put each day in single quotes and separate the days with comma and a space.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download