Data Download function CSV??
Moderator: General Moderators
Data Download function CSV??
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??
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??
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.
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.
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
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
and here is code snippet 2 by qads
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
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
{
$data .= "$username,$email\n";
}
$filename = "somefile.csv";
$handle = fopen($filename, "a");//will add to end of the file..
chmod($filename, 777);
fwrite($handle, $data);
fclose($filename);
?>Code: Select all
<?php
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
{
$data .= "$username,$email\n";
}
$filename = "somefile.csv";
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename='.$filename);
echo $data;
?>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ї'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))
{($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;
}
echo "</table>";
?>
Last edited by mohson on Fri Feb 25, 2005 10:48 am, edited 2 times in total.
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.csvmohson wrote:I dont get how you did it - you dont seem to have a download button?
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 extensionmohson 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.
See abovemohson wrote:I imagined there to be a button which when selected will download all the data.
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
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))
{
$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' ;
}
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename=contactlist.csv');
echo $data;download
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)) { $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' ; } header ('Content-type: text/csv'); header ('Content-Disposition: attachment; filename=contactlist.csv'); echo $data;
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
Re: download
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))
{
$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' ;
}
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename=contactlist.csv');
echo $data; //huff, finished here... ;)
?>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ї'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))
{
$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' ;
}
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"; ?>