How can I create a Word document from MySQL table data?

Question

I am generating a Word document from MySQL table data in PHP. For that purpose I wrote the following code:

$fh = fopen('C:\wamp\www\data.txt', 'w');
mysql_connect("localhost","root","admin");
 mysql_select_db("appulentoweb", $con);  

$result = mysql_query("SELECT * FROM freeadvice");
while ($row = mysql_fetch_array($result)) {
    $last = end($row);
    foreach ($row as $item) {
        fwrite($fh, $item);
        if ($item != $last)
            fwrite($fh, "\t");
    }
    fwrite($fh, "\n");
}
fclose($fh);

I alredy created an empty textfile named Data.txt, but the data is not getting into that file. Is there another alternative?

Answer 1

If you want, feel free to use this script that I put together for extracting large (sometimes in the gigabytes) CSV files from our datawarehouse here at work. It uses next to no system resources and is light enough to be nice and fast. I run it from a command line interface, so the output is written to make a nice simple display.

It will check the connection to make sure it can connect, then ensure that the query can be run (ie has no syntax errors) and extract the data into a CSV file for you giving you the query column names as the first line of the file. It keeps you informed of the row that it is on (to the nearest thousand) while it is running, so you can guess how much is left if you know the resultset expected size.

<?php

    // I have this set up for a few databases, so I keep all my usernames, passwords and extra options.
    // DWH
    $dwhusername = 'user';
    $dwhpassword = 'pass';
    $dwhhostname = 'oci:dbname=databaseName';
    $options = array(PDO::ATTR_AUTOCOMMIT=>FALSE);

    // EDW
    $edwusername = 'user';
    $edwpassword = 'pass';
    $edwhostname = 'oci:dbname=databaseName';

    // MySQL
    $musername = 'user';
    $mpassword = 'pass';
    $mhostname = 'mysql:host=localhost;dbname=databaseName';

    // Feel free to set this to anything you like. It is just used to give you a visual start and end time.
    date_default_timezone_set('Australia/Sydney');

    // Enter the filename that you want to save to below.
    $File = "C:\Server\yourFileName.csv";

    // Enter your SQL query that you use here.
    $sql="
    select
        col1 as Column1,
        col2 as Sales,
        col3 as TradeDate
    from
        myTable
    where
        col1=someCondition;
    ";

    function time_diff_conv($start, $s)
    {
        $string="";
        $t = array( //suffixes
            'd' => 86400,
            'h' => 3600,
            'm' => 60,
        );
        $s = abs($s - $start);
        foreach($t as $key => &$val) {
            $$key = floor($s/$val);
            $s -= ($$key*$val);
            $string .= ($$key==0) ? '' : $$key . "$key ";
        }
        return $string . $s. 's';
    }

    echo "\n\nStarting extract job on ".date('l jS \of F Y \a\t h:i:s A')."\n";


    $Handle = fopen($File, 'w');

    try{
        $dbh = new PDO($dwhhostname, $dwhusername, $dwhpassword, $options);
        //$dbh = new PDO($edwhostname, $edwusername, $edwpassword, $options);
        //$dbh = new PDO($mhostname, $musername, $mpassword);
        echo "Connection to database appears fine. Running query.\n";
        $timeQuery=time();
        $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $dbh->beginTransaction();
        $stmt = $dbh->query($sql);
        $Data="";
        $data2="";

        $obj = $stmt->fetch(PDO::FETCH_ASSOC);
        foreach($obj as $key => $value)
        {
            $Data.=$key.",";
            $data2.=$value.",";

        }
        $i++;
        $Data=substr($Data,0,-1);
        $Data.="\n".substr($data2,0,-1)."\n";
        $data2=null;
        fwrite($Handle, $Data);
        $timeResult=time();
        echo "Query Result Returned on ".date('l jS \of F Y \a\t h:i:s A')."\nExporting data now...\n\n";

        $Data="";

        while($obj = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            foreach($obj as $key => $value)
            {
                $Data.=$value.",";
            }
            $Data=substr($Data,0,-1);
            $Data.="\n";
            if($i%1000==0)
            {
                fwrite($Handle, $Data);
                $Data="";
                echo "\rWritten $i rows of data to the file.\r";
            }
            $i++;
        }
        fwrite($Handle, $Data);
        $Data="";
        $stmt=null;
        $dbh->commit();
        $dbh=null;

    }
    catch(PDOException $e){
        echo 'Error : '.$e->getMessage();
        exit();
    }

    $timeComplete=time();

    $timeQueryRes=time_diff_conv($timeQuery, $timeResult);
    $timeResRes=time_diff_conv($timeResult, $timeComplete);
    $timeAverage=number_format(round($i/($timeComplete-$timeResult+1),0));
    echo "Query took ".$timeQueryRes." to return a result\n";
    echo "The resultset of $i rows took ".$timeResRes." to completely extract at an average of ".$timeAverage." rows per second.\n";

    fclose($Handle);

    echo "Data written to: ".$File."\n";
    echo "Finished extract job on ".date('l jS \of F Y \a\t h:i:s A')."\n\n\n";

?>

The output in the terminal/console looks like this:

U:\>c:\server\wamp\bin\php\php5.3.0\php.exe -f "C:\server\wamp\www\store\inc\out
putData2.php"


Starting extract job on Thursday 19th of July 2012 at 04:25:44 PM
Connection to database appears fine. Running query.
Query Result Returned on Thursday 19th of July 2012 at 04:29:28 PM
Exporting data now...

Query took 3m 43s to return a resultfile.
The resultset of 1341447 rows took 4m 37s to completely extract at an average of
 4,825 rows per second.
Data written to: C:\Server\DailyStoreSales-2012-06-27.csv
Finished extract job on Thursday 19th of July 2012 at 04:34:05 PM



U:\>