export data from table into txt file

[ainbila]

hi everyone ,i export data from table in phpmyadmin to txtfile 

SELECT * FROM employees  INTO OUTFILE '/tmp/test.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY "" LINES TERMINATED BY ' ';

there is some problem

1) i dont want the symbol 

2) i want to keep column value when user not enter any value follow database structure 

 

 

between chia jun and date there is space because there have some column .

for example like in the image

 

 

 

 

[Barand]

1 hour ago, ainbila said:

1) i dont want the symbol

What symbol?

 

[ainbila]

the symbol is gone now . but how to remove the space ?

SELECT IFNULL(race_id,''), IFNULL(race_code,''), IFNULL(ptmk_race_code,''),IFNULL(race_name,''),IFNULL(keyin_by,''),IFNULL(keyin_date,'')
INTO OUTFILE 'pp.txt'
FIELDS TERMINATED by ''
from race

the data should follow the row value like in the db structure

like :

race_id  (1-11)

race_code(12-31)

 

now the output include many spaces : 

[Barand]

As you did not define a field terminator it looks like it may have defaulted to "tab" separators.

I prefer to use a php script to produce csvs and download them. MySql usually doesn't play nice when writing to files on the server.

TEST FILE

mysql> select * from client;
+-----------+-------+----------+----------+---------------+----------+-------------+---------------+
| client_id | title | fname    | lname    | address       | postcode | phone       | email         |
+-----------+-------+----------+----------+---------------+----------+-------------+---------------+
|         3 | Mr    | James    | Bond     | Thames House  | W1 2QQ   | 01232343456 | jb@mi6.gov.uk |
|         4 | Mrs   | Margaret | Thatcher | Gotham City   | GM1 3GG  | NULL        |               |
|         5 | Mr    | Scott    | Chegg    | Coronation St | NULL     | 16162554567 |               |
+-----------+-------+----------+----------+---------------+----------+-------------+---------------+

DOWNLOADED OUTPUT "client.csv"

client_id,title,fname,lname,address,postcode,phone,email
3,Mr,James,Bond,"Thames House","W1 2QQ",01232343456,jb@mi6.gov.uk
4,Mrs,Margaret,Thatcher,"Gotham City","GM1 3GG",,
5,Mr,Scott,Chegg,"Coronation St",,16162554567,

CODE

//
// sample usage
//
$con = new mysqli(HOST,USERNAME,PASSWORD,'tutorial2');

$sql_query = "SELECT * FROM client";

sql2csv ($con, $sql_query, 'client.csv', 1);   

//*****************************************************************************
// CSV download function
//*****************************************************************************
function sql2csv($con, $sql, $filename='', $headings=1)
        /**
        * Parameters
        * $con      -   connection
        * $sql      -   the sql query to be executed
        * $filename -   name of download file (default "download_yymmddhhii.csv")
        * $headings -   1 if fieldname headings required (default), 0 if not required
        */
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $con->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}