HTML download button for SQL query?

[Django x2]

Hi guys,

I want a button which, when users click, it downloads a csv file which returns all the results from a MySQL database.

Using PHP, here's what I had

<p>Export all contacts from within the database to a CSV file</p>

<form><input type="button" value="Download Now" onClick="<?php
SELECT * INTO OUTFILE 'c:/mydata.csv' FROM contacts;

?>"
</form>
<br />

But it's not working

Any ideas?

 

[topdog@OC]

First, it wouldn't download it, it would save it to a file on the MySQL database server.

Second, that's not PHP, you need to head over to the PHP website and check out how to establish a connection to a database and run queries against it.

Third, PHP runs on the server when it generates the web page - meaning, it would have already run the query and saved the file to the server before the user even sees the "Download Now" button. So you'll need to revise your program flow/logic a bit once you get going with the language.

 

[Django x2]

Ok, I should have added (but I didn't put it in the code). All the relevant connection data is at the top of the sheet linked to via an /includes/config.php file. There's already a connection to the database.

I thought that phpmyadmin used to show you the queries it ran when outputting to a file. I assume this would be sufficient, but I can't seem to find those queries anymore

 

[topdog@OC]

With the connection established you then need to run a query, fetch the data, and do something with it. Typically the code for that looks something like this: http://forums.overclockers.co.uk/showthread.php?p=20838941#post20838941

Your method with putting the file to a disk on the database server is prone to issues when multiple visitors try to download at once (the file may be corrupted or truncated for either user), or even just that it may try to serve the file to the user (with the appropriate code in place) before it has finished writing it to disk.

So I'd suggest the longer but contained method of looping through a results set to form a .csv response from another .php page that the Download Now button refers to is probably the way forward for you.

This is common ground for using MySQL and PHP though, so you should have no problem finding examples of code to do this on their sites. Start by looking up the mysql_query() function on the PHP site.

 

[Sir SpankalotUK]

http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv

seems pretty straight forward to do, forget saving the file somewhere, just return it dynamically.

 

[Django x2]

All the results already display, nicely, on the webpage. However, I need a "one button" click solution so that they can "export" the entire table to their machines for use within Excel

I looked at that solution, but it only seems to return the results

 

[Randomface74]

I do something similar via:

Page 1 has a button and an IFrame src="#"
Clicking the button sets the src of the IFrame to Page 2
Page 2 runs the query, sets the headers to tell the browser its returning a csv file, and prints the data.

headers:

header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

echo "record1,record2,record3\n";

 

[Django x2]

Cool, I've got it working now:

csv.php

<?php
session_start();
require_once('includes/config.php');

if(!$_SESSION['user']['username']) {
       //not logged in. redirect to login page
       header("Location: login.php");
       exit;
}
$result = mysql_query('SELECT * FROM `contacts`');  
if (!$result) die('Couldn\'t fetch records');  
$num_fields = mysql_num_fields($result); 
$headers = array();  
for ($i = 0; $i < $num_fields; $i++)  
{            
$headers[] = mysql_field_name($result , $i);
}  
$fp = fopen('php://output', 'w');
if ($fp && $result)  
{            
 header('Content-Type: text/csv');       
 header('Content-Disposition: attachment; filename="export.csv"');
 header('Pragma: no-cache');
 header('Expires: 0'); 
 fputcsv($fp, $headers); 
while ($row = mysql_fetch_row($result))
{ 
fputcsv($fp, array_values($row)); 
} die;
} 
 
?>

and the HTML

<form action="csv.php">
<input type="submit" value="Download Now">
</form>

 

[Randomface74]

 

[topdog@OC]

That's more like it !