Data Download function CSV??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Data Download function CSV??

Post 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??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Search........


i have already explained how to do this


viewtopic.php?p=123032#123032
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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 :)
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

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

?>
Last edited by mohson on Fri Feb 25, 2005 10:48 am, edited 2 times in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Noob suggested qads way would be easier can someone have a look at my new post above before 'Pimptatsics' reply please.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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:
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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;
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

just use fputcsv... because otherwise have to take care of doubling " yourself in values.....
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

download

Post 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;
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Re: download

Post 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... ;)
?>
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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"; ?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
Post Reply