Geek Answers Handbook

Export mysql table to .txt or .doc file using PHP

I have a mysql table that keeps a log of messages that users send every day. What I want to do is export the message log once a day to a text file, and I'm not sure how to do it. Our server has phpmyadmin, and I can export the table to a text file manually, but I don’t know how either A) have phpmyadmin export this file automatically once a day, or B) write the export to php code. I want the exported files to be available to users of my site for download. Website written in php. If there is any other information you need to answer this question, let me know!

+5
source share
5 answers

Use caution if you are not handling things like NULL, character sets, etc.

The first alternative:

<?php
$pdo = new PDO(...);
$results = $pdo->query("SELECT * FROM myTable INTO OUTFILE 'data.txt'");
$dummy = $result->fetchAll();

The data.txt file will be written to the MySQL server. The directory must be writable via the uid of the mysqld process. It will not overwrite any existing file and requires that you have FILESQL privilege .

Second alternative: use mysqldump to output to a text file (as @OMG Ponies mentioned):

mysqldump -t -T <directory> <database> <table>

It works like INTO OUTFILEit needs to be run on the MySQL server host, and the directory must be writable using mysqld uid.

Third option: run a query with the mysql client and print the text:

mysql -B -e "SELECT * FROM MyTable" <database> > mytable.txt

. NULL , mysqldump INTO OUTFILE.

+2
<?php
    $fh = fopen('data.txt', 'w');
    $con = mysql_connect("localhost","root","");
    mysql_select_db("db_name", $con);

    /* insert field values into data.txt */

    $result = mysql_query("SELECT * FROM table_name");   
    while ($row = mysql_fetch_array($result)) {          
        $last = end($row);          
        $num = mysql_num_fields($result) ;    
        for($i = 0; $i < $num; $i++) {            
            fwrite($fh, $row[$i]);                      
            if ($row[$i] != $last)
               fwrite($fh, ", ");
        }                                                                 
        fwrite($fh, "\n");
    }
    fclose($fh);
?>
+5

PHP script .txt. , - . :

<?
$fh = fopen('data.txt', 'w');
mysql_connect('host', 'username', 'password');
$result = mysql_query("SELECT * FROM myTable;");
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);
?>
+4

, - script , , , "mysql_select_db" "mysql_query". "myTable (;)". , . script . , script, .   

0

The answer I posted earlier will only work fine when the last field value in the row is not equal to any other field value in the same row. Bcoz the comma separation check is based on the last field value, now it has changed for the last field index.

If u uses the previous code, then u cannot get the correct comma separation near the field value, which is equal to the last field value in this line, in other ways the code is also correct.

<?php
    $fh = fopen('data.txt', 'w');
    $con = mysql_connect("localhost","root","");
    mysql_select_db("db_name", $con);

    /* insert field values into data.txt */

    $result = mysql_query("SELECT * FROM table_name");   
    while ($row = mysql_fetch_array($result)) {          
        $num = mysql_num_fields($result) ;    
        $last = $num - 1;
        for($i = 0; $i < $num; $i++) {            
            fwrite($fh, $row[$i]);                       
            if ($i != $last) {
                fwrite($fh, ",");
            }
        }                                                                 
        fwrite($fh, "\n");
    }
    fclose($fh);
?>
0
source

More articles:


All Articles