Format exported data from database to a text file-CodePudding

is it possible to group and write a data from SQL to a text file in a desired format? If so, how and what is the proper approach for this? This is the current code I have and users can select which month and year of data should be exported.

$user = $sanitize->for_db($_POST['user']);
$date = $_POST['date'];
$period = explode("-", $date);
    
$row = $database->get_assoc("SELECT * FROM `table_name` WHERE `user`='".$user."' AND YEAR(date)='".$period[0]."' AND MONTH(date)='".$period[1]."' ORDER BY `date` DESC");
    
if( !$row )
{
    $error[] = "Sorry, there was an error and the data was not exported.";
}

else
{
    $file = 'path/to/'.$user.'.txt';
    $fh = fopen($file, 'a');
    $last = end($row);
            
    foreach( $row as $item )
    {
        fwrite($fh, $item);
        if( $item != $last )
        {
            fwrite($fh, " | ");
        }
    }
    fwrite($fh, "\n");
    fclose($fh);

    $success[] = "Your data has been exported successfully!";
}

However, this code will write the data as:

User | lorem ipsum | 2022-02-15
User | dolor sit amet | 2022-02-15
User | consectetur adipiscing elit | 2022-02-20

So, is it possible to get a desired format like the one below?

2022-02-15 -----
- lorem ipsum
- dolor sit amet

2022-02-20 -----
- consectetur adipiscing elit

Any answer/help is highly appreciated! Many thanks in advance!

CodePudding user response：

Thank you Mehrdad for that brilliant question! It gave me an idea how to solve this hurdle.

I changed the foreach loop to while loop to get my desired format.

$user = $sanitize->for_db($_POST['user']);
$date = $_POST['date'];
$period = explode("-", $date);

$row = $database->query("SELECT * FROM `table_name` WHERE `user`='$user' AND `date` LIKE '$date-%' ORDER BY `date` DESC");

if( !$row )
{
    $error[] = "Sorry, there was an error and the data was not exported.";
}

else
{
    $file = 'path/to/'.$user.'.txt';
    $fh = fopen($file, 'a');
    $timestamp = '';
    $output = '';

    while( $r = mysqli_fetch_assoc( $row ) )
    {
        if( $r['date'] != $timestamp )
        {
            $output = $output."".date('F d, Y', strtotime($r['date']))." -----\n";
            $timestamp = $r['date'];
        }
        $output = $output."- ".$r['data']."\n";
    } // end while
        
    fwrite($fh, $output);
    fclose($fh);

    $success[] = "Your data has been exported successfully!";
}

CodePudding user response：

SELECT CONCAT(`date`,
              ' -----',
              GROUP_CONCAT('\n- ', `text_column` SEPARATOR ''))
FROM `table_name`
WHERE `user`='$user' 
  AND YEAR(`date`)='$period[0]' 
  AND MONTH(`date`)='$period[1]'
GROUP BY `date`
ORDER BY `date`

INTO OUTFILE 'X:\\folder\\output.txt'
    LINES TERMINATED BY '\n\n'
;

This code will select your data in needed format and save into text file.

Alternatively you may select this in your program (remove INTO OUTFILE part, add an alias to the output expression) and save the output into text file as provided, without additional processing.