Page 1 of 1

Download Function

Posted: Wed Jul 19, 2006 4:59 am
by mohson
I tried to attemt this about a year ago but never got anywhere, Im going to give it another go and would really appreciate the help.

Basically I have a system which displays records from my database, my team want to be able to download this data into an excel spreadsheet to use it as a source for mailouts.

I want to create a button on all my screens called download.

This button when selected will down load all the 'backend' data from the page being viewed and export it.

Now when I tried this previously I came up with something like this:

Download button pointed to the current page url/export_csv.php

the code for this link is:

Code: Select all

<?php
header ("Content-type: text/csv"); 

/* Connecting, selecting database */
$link = mysql_connect("xxxxx", "xxxx", "xxxxx")
   or die("Could not connect : " . mysql_error());
mysql_select_db("contact_management_system",$link) or die("Could not select database");



 

 
$query = "SELECT * FROM people ORDER BY organisation";
 
$result = mysql_query($query) or die(mysql_error());
 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
	
	printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
	$line['person_id'],$line['salutation'],$line['firstname'],
	$line['surname'],$line['organisation'],$line['role'],
    $line['address1'],$line['address2'],$line['city'],$line['postcode'],
	$line['telephone'],$line['mobile'],$line['fax'],
	$line['dateoflastcontact'],$line['datecontactagain'],
	$line['notes'],$line['email'],$line['org_id']
	);

 
?>


But all this does is print a load of data on the screen.

Any help or advice would be greatly appreciated.

Also there doesnt seem to be much information on this, I thought it would be a really common procedure for people to download there data for mailouts??

Posted: Wed Jul 19, 2006 5:01 am
by Ollie Saunders
tried viewing the source of the output?

Posted: Wed Jul 19, 2006 5:10 am
by mohson
Ok I selected View>>>page source and I got a window which looks like a text file with all the data seperated by commas? what do you suggest I do now? or how will this help.

Please excuse my lack of awareness

Posted: Wed Jul 19, 2006 5:34 am
by Ollie Saunders
save it as csv and import it into excel :)

Posted: Wed Jul 19, 2006 5:44 am
by Jenk
also specify the header:

Code: Select all

header('Content-Disposition: attachment; filename=<nameoffilegoeshere>.csv;');

Posted: Wed Jul 19, 2006 5:52 am
by mohson
This seems to work. When I veiw source of page from the menu a get a window which looks liks a .txt file. I save this as a .csv file and BINGO!!! I have all my data in an excel type spread sheet, Exactl what I need.

Problem is users will not allways want to down load all the data. Users may want to download data according to a query they have run.

but when I change the query it still loads up all data as opposed to the data I am currently viewing.

Any advise or tips please?

heres my new code

Code: Select all

<?php
header ("Content-type: text/csv"); 

/* Connecting, selecting database */
$link = mysql_connect("xxxx", "xxxx", "xxxxxx")
   or die("Could not connect : " . mysql_error());
mysql_select_db("contact_management_system",$link) or die("Could not select database");



 foreach($HTTP_POST_VARS as $varname => $value)
        $formVars[$varname]=$value;

$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.email,

		DATE_FORMAT(dateoflastcontact, '%M/%Y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%M/%Y') 
		AS datecontactagain 

		
		FROM people p LEFT JOIN organisations o
     		ON o.org_id = p.org_id

		WHERE firstname LIKE '$formVars[firstname]%'";


$result = mysql_query($query);

$result = mysql_query($query) or die(mysql_error());
 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
	
	printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
	$line['person_id'],$line['salutation'],$line['firstname'],
	$line['surname'],$line['organisation'],$line['role'],
    $line['address1'],$line['address2'],$line['city'],$line['postcode'],
	$line['telephone'],$line['mobile'],$line['fax'],
	$line['dateoflastcontact'],$line['datecontactagain'],
	$line['notes'],$line['email'],$line['org_id']
	);

 
?>
any ideas why? might be happening?

Posted: Wed Jul 19, 2006 5:57 am
by mohson
Jenk wrote:also specify the header:

Code: Select all

header('Content-Disposition: attachment; filename=<nameoffilegoeshere>.csv;');
Thanks Jenk,

How will this help?

What filename shall I put in there the name of the current file with the download cod in it or the file that I am viewing?

Tried both nothing seems to change