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 — Apb
– Apb, Commented May 18, 2016 at 6: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
– jay, Commented May 18, 2016 at 7:00
Possible duplicate of Easy way to export a SQL table without access to the server or phpMyADMIN — Cliff Burton
– Cliff Burton, Commented May 18, 2016 at 7:07
I edited my post, I almost found a solution :) just one problem left, save on local computer instead of on server directory — jay
– jay, Commented May 18, 2016 at 8:35

Devsi Odedra · Accepted Answer · 2016-05-18 07:08:38Z

 <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 · Accepted Answer · 2016-05-18 09:14:19Z

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, 2016 at 9:14

answered May 18, 2016 at 7:50

Barry

3,3287 gold badges26 silver badges43 bronze badges

4 Comments

jay Over a year ago

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';";

`

Barry Over a year ago

@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

jay Over a year ago

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

Barry Over a year ago

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

jay · Accepted Answer · 2016-05-18 11:31:21Z

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);
?>

Collectives™ on Stack Overflow

Export MySQL Database as Excel CSV to Desktop in PHP

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related