Export MySQL Database as Excel CSV to Desktop in PHP

Question

I've been looking for ways to enable EXPORT feature for my database website. My goal is to construct a very simple PHP code to do this, all it needs is to export the contents of one of my MySQL tables to the user's desktop or give the user a clickable link so he can download the exported database. The DB must be in Excel/CSV (As I will be using this feature for import/export DB backup feature as well).

I saw fputcsv() and other old methods like mysql (instead of mysqli) codes but all fails, some created a CSV file on server's directory while other methods simple gives an error like

explode() expects parameter 2 to be string, object given in

or

fputcsv() expects parameter 2 to be array, null given in

I feel this should have been simple but what is the best way or code to use for export DB feature?

EDIT:

After combining and testing series of options, I was able to solve it. I am sharing here so those who will experience my dilemma will not have a hard time like I did :(

When the user clicks the EXPORT button, Chrome browser automatically downloads a CSV file which contains a copy of the database including the field names.

<?php
    include('/connectme.php');

    $thequery = "SELECT * FROM members";
    $result = mysqli_query($conn,$thequery);

    $file = fopen("php://output","w");

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="exported.' . date("Y.m.d") . '.csv"');
    header('Pragma: no-cache');    
    header('Expires: 0');

    $emptyarr = array();
    $fieldinf = mysqli_fetch_fields($result);
        foreach ($fieldinf as $valu)
    {
        array_push($emptyarr, $valu->name);
        }
    fputcsv($file,$emptyarr);

    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($file,$row);
    }
    fclose($file);

    mysqli_free_result($result);
    mysqli_close($conn);
?>

Possible duplicate of [Export a mysql table into CSV using PHP code](http://stackoverflow.com/q/17362686/2156244) — Apb, May 18 '16 at 06:56
That's the old version mysql though, I saw a similar sample and tried copying it, aside from the warning of deprication of mysql, it gave some errors. No new way of doing this? — jay, May 18 '16 at 07:00
Possible duplicate of [Easy way to export a SQL table without access to the server or phpMyADMIN](http://stackoverflow.com/questions/81934/easy-way-to-export-a-sql-table-without-access-to-the-server-or-phpmyadmin) — Cliff Burton, May 18 '16 at 07:07
I edited my post, I almost found a solution :) just one problem left, save on local computer instead of on server directory — jay, May 18 '16 at 08:35

score 0 · Answer 1 · answered May 18 '16 at 07:08

 <form  class="elegant-aero" action="backup.php">

       <center>
       <h1>SQL Server Backup And Restore

    </h1>
   <style>
   .btn{
        width:80px;
        height:30px;
        background:#0B49D8;
        color:#FFFFFF;

     }
   </style> 

   <label>

        <input id="name" type="submit" name="backup" class="btn" value="Backup" />


    </label>    

        </form>

**php code is below. you can change extension .sql or .csv and change tha path where you want to save backup file. currently backup save in you files near where your code files are places. dabase backup file name is stock that you can change **

<?php
error_reporting(E_ALL ^ E_DEPRECATED);

error_reporting(error_reporting() & ~E_NOTICE);
backup_tables('localhost','root','','stock1');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);

    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    //cycle through
    foreach($tables as $table)
    {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        $return.= 'DROP TABLE '.$table.';';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";

        for ($i = 0; $i < $num_fields; $i++) 
        {
            while($row = mysql_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j < $num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j < ($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }

    //save file
    $handle = fopen('stock'.'.csv','w+');
    fwrite($handle,$return);
    fclose($handle);
    if($handle)
{   
//$msg="Insert Successfully!!";
echo "<script>alert('Backup success');document.location='home.php'</script>";
?>

<?php
}
else
{

//$errmsg=" Insert not success!!";
      echo "<script>alert('Backup not success');document.location='home.php'</script>";

}
}
?>

Barry · Answer 2 · 2016-05-18T09:14:19.540

0

A more concise and faster option is use of INFILE / OUTFILE. So in PHP call the following SQL query...

$db_conn = mysql_connect('localhost','root','');
mysql_select_db('stackoverflow',$db_conn);

$q = "
    SELECT * FROM mytable INTO OUTFILE '/mytable.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"'
    LINES TERMINATED BY '\n';
";

$result = mysql_query($q,$db_conn);

This is the fastest method to then import from afterwards with the data.

edited May 18 '16 at 09:14

answered May 18 '16 at 07:50

Barry

3,109
7
20
40

I seem to get an error: `Parse error: syntax error, unexpected '' LINES TERMINATED BY '' (T_CONSTANT_ENCAPSED_STRING) in` The line was `$thequery = "SELECT * FROM members INTO OUTFILE '/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';";` ` – jay May 18 '16 at 08:42
@jay well I've updated it so that it is now 100% tested, please excuse use of mysql_connect it isn't the focus point of this script, running $q is. also this outputs to the root of the drive on windows – Barry May 18 '16 at 09:15
I didn't get any errors this time but I don't see any mytable.csv files anywhere on the server's directory or anywhere in my PC :D – jay May 18 '16 at 09:25
oh dare, well as I said I've 100% tested this... if you are on a unix system then you it should be in the root, but you can adjust '/mytable.csv' to be a full path to where you like – Barry May 18 '16 at 09:26

score 0 · Answer 3 · answered May 18 '16 at 11:31

After combining and testing series of options, I was able to solve it. I am sharing here so those who will experience my dilemma will not have a hard time like I did :(

When the user clicks the EXPORT button, Chrome browser automatically downloads a CSV file which contains a copy of the database including the field names.

<?php
    include('/connectme.php');

    $thequery = "SELECT * FROM members";
    $result = mysqli_query($conn,$thequery);

    $file = fopen("php://output","w");

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="exported.' . date("Y.m.d") . '.csv"');
    header('Pragma: no-cache');    
    header('Expires: 0');

    $emptyarr = array();
    $fieldinf = mysqli_fetch_fields($result);
        foreach ($fieldinf as $valu)
    {
        array_push($emptyarr, $valu->name);
        }
    fputcsv($file,$emptyarr);

    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($file,$row);
    }
    fclose($file);

    mysqli_free_result($result);
    mysqli_close($conn);
?>

Export MySQL Database as Excel CSV to Desktop in PHP

3 Answers3