Export mysql table column as newline-separated values in text file

Question

I have this script to output into a text file:

$sql = "SELECT bodyipaddress from email_body";
$query = $dbh -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
$cnt=1;
if($query->rowCount() > 0)
{
    foreach ($results as $result)   
    {
        $bodyipaddress       = $result->bodyipaddress;
        // create a txt file 

        $myfile      = fopen("file.txt", "a");
        $padded_text = str_pad($bodyipaddress, 0, '', STR_PAD_RIGHT);
        fwrite($myfile, $bodyipaddress);
        fclose($myfile);
                                        
        $cnt=$cnt+1; 
    }    
} 

But the data's result when read through the text file was a plain text and the formatting has disappeared. Was it possible for me to style the text vertically by using p

Below is the output that I want to change to vertically for each white spaces found.

Answer 1

It looks like you are exporting a columnar set of data from the entire email_body table, so the most elegant/direct approach IMO is:

1. Execute a standard pdo query -- since there are no parameters to bind values to, a prepared statement is needless overhead.
2. Form the result set into a flat array using PDO::FETCH_COLUMN.
3. Since it only makes sense to create or totally overwrite the file, I do not recommend the a mode on fopen() -- w seems more appropriate so that there will be no appended data. In fact, you may even prefer to call file_put_contents() as a single function call versus fopen(), fwrite(), then fclose().
4. Implode the flat array of ip addresses with PHP_EOL to align the newline sequence with your system environment (\r\n or \n).
5. You aren't using $cnt in your posted script, so that manually incremented tally can be completely omitted. If you want to know how many ip addresses are in the array, just call count() on the result set array.
6. I don't understand why you are trying to right-pad your ip addresses with empty spaces to a length of zero characters.

New code:

file_put_contents(
    "file.txt",
    implode(
        PHP_EOL,
        $dbh->query("SELECT bodyipaddress FROM email_body")->fetchAll(PDO::FETCH_COLUMN)
    )
);

...Yes, it actually is just that simple/concise.

---

If your bodyipaddress actually has comma&space-separated values, then you can replace them with new lines in the sql or in php. I don't know what your table data really looks like.

file_put_contents(
    "file.txt",
    str_replace(
        ', ',
        PHP_EOL,
        implode(
            PHP_EOL,
            $dbh->query("SELECT bodyipaddress FROM email_body")->fetchAll(PDO::FETCH_COLUMN)
        )
    )
);