[SOLVED] Download and save a local copy of CSV from MySQL?

[deanes02]

Hi,  I have done a lot of seraching on this and I can find code to create a server side copy of a csv file from MySQL.  Although this doesn't work for me as access is denied to do this.

 

What i want to do is with a button click download to contents of a table into a new local csv file (not adding the content to an existing file or anything).  I know how to create the code to generate the csv from the MySQL [i.e. traverse the table adding commas at the end of each line and a return after the end of a line] but its the downloading and saving locally that i have difficulty with.

 

Also if this download could happen automatically every night that would be a nice bonus.

 

Sorry if I'm unclear.  Please let me know.

 

Thanks,

S

[AndyB]

I've used this complete script successfully. The important bit for you is what happens AFTER the results string is generated.

 

<?php 
include("../includes/db_conn.php");
mysql_connect($db_host, $db_login, $db_pass) or die ("Can't connect!"); 
mysql_select_db($db_name) or die ("Can't open database!"); 
if (!isset($_GET['ward'])) {
    header("Location: index.php");
    exit();
}
$ward=$_GET['ward'];
$CSV = "IP,Name,Address,PostCode,Phone,Email,Ward,Date\n";
$s_bit = "";
if ($ward!=0) { $s_bit="WHERE ward = '$ward' "; }
$p = "subwayresults_";
if ($ward!=0) {
    $p.= "ward-". $ward;
} else {
    $p.="all-wards";
}
$p.="_". date("Y-m-d"). ".txt";

$query = "SELECT * FROM $db_table ". $s_bit. "ORDER by ward,dated ASC";
$result = mysql_query($query);
while($myrow = mysql_fetch_array($result)) {
    $CSV.= $myrow[ip]. ",";
    $CSV.= $myrow[name]. ",";
    $CSV.= $myrow[address]. ",";
    $CSV.= $myrow[postcode]. ",";
    $CSV.= $myrow[phone]. ",";
    $CSV.= $myrow[email]. ",";
    $CSV.= $myrow[ward]. ",";
    $CSV.= $myrow[dated]. "\n";
}

// generate the file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$p\"");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    // IE cannot download from sessions without a cache
    header('Cache-Control: public');
}
echo $CSV;
exit;
?>

 

That should get you started.

[deanes02]

Thanks for your help so far.

 

Below is my code and I've tacked on your bit to the bottom.  Although the csv file is displayed nicely within IE no file, as far as I'm aware, has been created.  I was expecting to be asked "Do you want to save this file?" or some such thing.  I could save the page generated by the script through IE but thats not quite as slick as i was hoping to get.

 

Am I missing something?  Thanks, S

 

//=================================set up query to search those tables
$query = select * from wpctchie WHERE JobRef LIKE '%%' ORDER BY JobRef ;
//echo "$query<br>";
$result = mysql_query($query,$connection)
or die ("<b><font color='red'>Sorry.  Could not execute your request this time. 6 </font></b>".mysql_error());


            //display the field names
            for($i = 0;$i < mysql_num_fields($result);$i++)
            {
             $row = mysql_fetch_row($result);
             //echo mysql_field_name($result,$i).",";
             $csv .= mysql_field_name($result,$i).",";
            }
            //echo "\n";
            $csv .= "\n";
            
            //display the csv portion   
            for ($i = 0; $i < mysql_num_rows($result); $i++)
            {  
              $row = mysql_fetch_row($result);  
              for($j = 0;$j<mysql_num_fields($result);$j++) 
              {
                  //echo $row[$j] . ",";
                  $csv .= $row[$j] . ",";
              }
              //echo "\n";
              $csv .= "\n";
            }
            echo $csv;
            
mysql_close($connection);

// generate the file
$p.="SalesOrderDB_". date("Y-m-d"). ".csv";

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$p\"");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    // IE cannot download from sessions without a cache
    header('Cache-Control: public');
}
echo $csv;
exit;

[deanes02]

My Apologies... this code actually does work.  perfectly in fact!  Once i cleared the cache in IE it all came good.

 

thanks a million for your help.

[AndyB]

Glad to hear it.  Like you, I couldn't find out how to do this and eventually stumbled on the code somewhere else from which I cobbled together the version I posted.

[fenway]

Just be careful about non-text fields.