Page 1 of 1

Data Download function CSV??

Posted: Fri Feb 25, 2005 9:12 am
by mohson
I want to create a button on my web application which allows users to download all the data which is being displayed on the page they are looking at.

for example if they are viewing all records they can click a download button and download all contact data to a spreadsheet (for purposes of a mail merge)

Ok so thats the problem domain ... ......

The advice I have recieved so far is that it would be tricky to download staright to excel and that I should download to CSV.

Could anyone give me any advice, tips , tutorials or help on how to create this fully functional download button??

Posted: Fri Feb 25, 2005 9:17 am
by feyd
pass required query information through the button to a script that responds by telling the browser it's sending a CSV file. Perform the necessary query, then compact it into a CSV format. Echo the compacted data to the client. Done.

Posted: Fri Feb 25, 2005 9:27 am
by JayBird
Search........


i have already explained how to do this


viewtopic.php?p=123032#123032

Posted: Fri Feb 25, 2005 9:59 am
by mohson
I dont get how you did it - you dont seem to have a download button?

I created a a new file exactly the same as the file which currently displays my results - except I named it exportcsv.php I then thought perhaps I should name it exportcsv.php/export.csv but you cant save a file with '/', I know im being slow about this but more advice would be good.

I imagined there to be a button which when selected will download all the data.

Posted: Fri Feb 25, 2005 10:17 am
by n00b Saibot
See qads post there. That method should be easy for ya.
jus' write all the data (fields separated by a comma) to a file with csv extension and post a link to that file
so that user will download it by clicking on that link :)

Posted: Fri Feb 25, 2005 10:39 am
by mohson
sorry to make this painstaking but im struggling bad. below is qads code he has given two code snippets are they both the same or is he describing two different methods? code snippet one is below

Code: Select all

<?php
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
&#123;
$data .= "$username,$email\n";
&#125;
$filename = "somefile.csv";
$handle = fopen($filename, "a");//will add to end of the file..
chmod($filename, 777);
fwrite($handle, $data);
fclose($filename);
?>
and here is code snippet 2 by qads

Code: Select all

<?php
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
&#123;
$data .= "$username,$email\n";
&#125;
$filename = "somefile.csv";
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename='.$filename);
echo $data;
?>
what I have done so far is create a file called exportcsv.php

the content is exactly the same as when I display records all I need to do now is download the data (I think this is the correct way of doing it??

below is my code could you please advise me on how I would incoporate qads's method (shown on the link in the previous reply) into my code??

I would be so grateful:

my code:

Code: Select all

<?php


// config-------------------------------------
$host = "****.ac.uk"; //your database host
$user = "***"; // your database user name
$pass = "*****"; // your database password
$db = "contact_management_system"; // your database name

$filename = "people.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 100; // default number of records per page
$action = $_SERVER&#1111;'PHP_SELF']; // if this doesn't work, enter the filename


// database query. Enter your query here

 $query = 	"SELECT 
		o.org_id,o.web_url,
		p.person_id,p.org_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
		p.datecontactagain,p.notes,p.email, 

		DATE_FORMAT(dateoflastcontact, '%d/%M/%y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%d/%M/%y') 
		AS datecontactagain 

		FROM people p LEFT JOIN organisations o
     		ON o.org_id = p.org_id		
		ORDER BY firstname";


// end config---------------------------------

// Query to extract records from database.
$sql = mysql_query ("$query LIMIT $limit") or die ("Error in query: $sql".mysql_error());


// Define your colors for the alternating rows

$color1 = "#ADD8E6";$color2 = "#E0FFFF";
$color = $color2;echo 

"<table width="50%" border="0" cellpadding="2" cellspacing="2">

    <tr>
		<td><b><small>RecNo</small></b></td>
		<td><b><small>PID</small></b></td>
		<td><b><small>OID</small></b></td>
		<td><b><small>Title</small></b></td>
		<td><b><small>First Name</small></b></td>
		<td><b><small>Surname</small></b></td>
		<td><b><small>Organisation</small></b></td>
		<td><b><center><small>Role</small></center></b></td>
		<td><b><small>Address(1)</small></b></td>
		<td><b><small>Address(2)</small></b></td>
		<td><b><small>City</small></b></td>
		<td><b><small>Post Code</small></b></td>
		<td><b><small>Telephone</small></b></td>
		<td><b><small>Mobile</small></b></td>
		<td><b><small>Fax</small></b></td>
		<td><b><small>Last Contact</small></b></td>
		<td><b><small>Contact Again</small></b></td>
		<td><b><small>Notes</small></b></td>";




while ($row = mysql_fetch_object($sql)) 







&#123;($color==$color2)? $color = $color1 : $color = $color2;


echo "<tr bgcolor="$color"><td>".$count . '</td><td> ' . $row->person_id .'</td><td>'.        
	
	$row->org_id.'</td><td>'. 
	$row->salutation .'</td><td>'.         
	
	'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
	'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.       
	
	'<a href=http://'.$row->web_url.'>'.$row->organisation . '</a></td><td>'.

	
	$row->role.'</td><td>'.       
 	$row->address1 .'</td><td>'.        
	$row->address2 .'</td><td>'.       
 	$row->city .'</td><td>'.       
 	$row->postcode .'</td><td>'.        
	$row->telephone .'</td><td>'.        
	$row->mobile .'</td><td>'.        
	$row->fax .'</td><td>'.        
	$row->dateoflastcontact.'</td><td>'.        
	$row->datecontactagain.'</td><td>'.
	$row->datecontactagain.'</td><td>';

$count += 1;
&#125;

echo "</table>"; 

?>

Posted: Fri Feb 25, 2005 10:39 am
by JayBird
mohson wrote:I dont get how you did it - you dont seem to have a download button?
Im sure you can create a button yourself. Either a form button or a HTML link that points to the script with a link like -http://192.168.1.2/ocdaintranet/contact ... export.csv

mohson wrote:I created a a new file exactly the same as the file which currently displays my results - except I named it exportcsv.php I then thought perhaps I should name it exportcsv.php/export.csv but you cant save a file with '/', I know im being slow about this but more advice would be good.
The / is a trick, if you didnt do this, the file you downloaded would have a PHP extension, this trick make the browser use a CSV extension
mohson wrote:I imagined there to be a button which when selected will download all the data.
See above

Posted: Fri Feb 25, 2005 10:50 am
by mohson
Noob suggested qads way would be easier can someone have a look at my new post above before 'Pimptatsics' reply please.

Posted: Fri Feb 25, 2005 11:01 am
by JayBird
mohson wrote:Noob suggested qads way would be easier can someone have a look at my new post above before 'Pimptatsics' reply please.
My way is FAR easier :wink:

Posted: Sat Feb 26, 2005 2:20 am
by n00b Saibot
OK, Here's My Way ;)

Code: Select all

$data = 'RecNo,PID,OID,Title,First Name,Surname,Organisation,Role,Address,Address,City,Post Code,Telephone,Mobile,Fax,Last Contact,Contact Again,Notes\r\n'; 

while ($row = mysql_fetch_object($sql)) 
&#123;
$rec = array($count++, $row->person_id, $row->org_id, $row->salutation, $row->firstname, $row->surname, $row->organisation, $row->role, $row->address1, $row->address2, $row->city, $row->postcode, $row->telephone, $row->mobile, $row->fax, $row->dateoflastcontact, $row->datecontactagain, $row->notes);
$data .= implode(',',$rec).'\r\n' ;
&#125; 
header ('Content-type: text/csv'); 
header ('Content-Disposition: attachment; filename=contactlist.csv'); 
echo $data;

Posted: Sat Feb 26, 2005 5:32 am
by timvw
just use fputcsv... because otherwise have to take care of doubling " yourself in values.....

download

Posted: Mon Feb 28, 2005 7:29 am
by mohson
Noob how do I use this I created a new file and called it exportscv I then added my connection code but nothing happens how do I use this??
n00b Saibot wrote:OK, Here's My Way ;)

Code: Select all

$data = 'RecNo,PID,OID,Title,First Name,Surname,Organisation,Role,Address,Address,City,Post Code,Telephone,Mobile,Fax,Last Contact,Contact Again,Notes\<span style='color:blue' title='ignorance is bliss'>are</span>\n'; 

while ($row = mysql_fetch_object($sql)) 
&#123;
$rec = array($count++, $row->person_id, $row->org_id, $row->salutation, $row->firstname, $row->surname, $row->organisation, $row->role, $row->address1, $row->address2, $row->city, $row->postcode, $row->telephone, $row->mobile, $row->fax, $row->dateoflastcontact, $row->datecontactagain, $row->notes);
$data .= implode(',',$rec).'\<span style='color:blue' title='ignorance is bliss'>are</span>\n' ;
&#125; 


header ('Content-type: text/csv'); 
header ('Content-Disposition: attachment; filename=contactlist.csv'); 
echo $data;

Re: download

Posted: Mon Feb 28, 2005 7:48 am
by n00b Saibot

Code: Select all

<?php
mysql_connect('xxxxxxxxx','xxxx','xxxxxxx');
mysql_select_db('contact_management_system");
$sql = 'SQL QUERY HERE';
$result = mysql_query($sql) or die('Error: '.MySQL_error().'<br>SQL: '.$sql);

$data = 'RecNo,PID,OID,Title,First Name,Surname,Organisation,Role,Address,Address,City,Post Code,Telephone,Mobile,Fax,Last Contact,Contact Again,Notes\<span style='color:blue' title='ignorance is bliss'>are</span>\n'; 

while ($row = mysql_fetch_object($result)) 
&#123;
$rec = array($count++, $row->person_id, $row->org_id, $row->salutation, $row->firstname, $row->surname, $row->organisation, $row->role, $row->address1, $row->address2, $row->city, $row->postcode, $row->telephone, $row->mobile, $row->fax, $row->dateoflastcontact, $row->datecontactagain, $row->notes);
$data .= implode(',',$rec).'\<span style='color:blue' title='ignorance is bliss'>are</span>\n' ;
&#125; 

header ('Content-type: text/csv'); 
header ('Content-Disposition: attachment; filename=contactlist.csv'); 
echo $data; //huff, finished here... ;)
?>

Posted: Mon Feb 28, 2005 8:40 am
by mohson
Noob, Ivealready done this below (is the code ok?) now what im asking is that all I need to do because when I link to this page all I get is a blank white page with the column headers - any ideas - how does this actually download data???

Code: Select all

<?php


// config-------------------------------------
$host = "vega.soi.city.ac.uk"; //your database host
$user = "af342"; // your database user name
$pass = "010098552"; // your database password
$db = "contact_management_system"; // your database name

$filename = "people.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 100; // default number of records per page
$action = $_SERVER&#1111;'PHP_SELF']; // if this doesn't work, enter the filename



$data = 'RecNo,PID,OID,Title,First Name,Surname,Organisation,Role,Address,Address,City,Post Code,Telephone,Mobile,Fax,Last Contact,Contact Again,Notes\are\n';

while ($row = mysql_fetch_object($sql))
&#123;
$rec = array($count++, $row->person_id, $row->org_id, $row->salutation, $row->firstname, $row->surname, $row->organisation, $row->role, $row->address1, $row->address2, $row->city, $row->postcode, $row->telephone, $row->mobile, $row->fax, $row->dateoflastcontact, $row->datecontactagain, $row->notes);
$data .= implode(',',$rec).'\are\n' ;
&#125;
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename=contactlist.csv');
echo $data; 

?>

	

<p> 
  <!-- Content End   -->
  <!-- Please don't remove these comments -->
  <?php include "dyn.footer"; ?>

Posted: Mon Feb 28, 2005 8:47 am
by feyd
those HTML comments would be "garbage" in the CSV..

You are using single-quote strings. This makes the carriage return characters not be processed into their actual bits..

btw, you could use mysql_fetch_row() instead of object..