Generating A Table Into A File In CSV Format and letting user download the file

[abhiram 0]

I'm working on a project, part of which consists of working with large tables of different kinds. Now, I'm using a page seeking technique which allows you to browse through the records depending on which page you want to see and how many records you want to see on each page. Now, I need a link (or a form button) on each page which, when clicked, will throw a file to the user for download (the download window should popup immediatly) which will give the part of the table, currently being viewed, in CSV format.One way I can think of to generate the file is manually, that is, open an empty file in a temp directory and output everything into it using 'fwrite' in the way I want it. Is there any PHP or MySQL function which gives it directly?But, how do I get the user to download this file? I want it to immediatly open the download window saying "Do you want to download this file" as it does in firefox, rather than take him to another page with a link to the file.Any ideas?Thanks.

[iGuest 3]

Here is a PHP Script which may help you.

 

First of all, Let us create a table named tbl_test in a MySQL Database. The table contains three fields:

 

1. field_id

2. category

3 description

 

Now here is the CREATE TABLE CODE for you.

 

First of all, we'll drop Table, if it exists.

 

DROP table if exists `tbl_test`;

Now we'll create the table tbl_test.

 

CREATE TABLE `tbl_test` (  `field_id` int(11) NOT NULL auto_increment,  `category` char(20) default 'General',  `description` char(100) default 'Not Specified',  PRIMARY KEY  (`field_id`) )

Now insert some test data in the table.

 

Now is the time to play with the following PHP Script.

 

The script starts at this point.

 

<?php 		global $db_name,$db_host,$db_user_name,$db_pass;		$db_name='test';				 		$db_host='localhost';		$db_username='test';		$db_pass='test';

Connecting to Database:

$new_file="myfile.csv";

Consider giving a dynamic file name rather than a static one.

This is the file that will store the fetched data.

 

Create a file using fwrite():

 

$fp=fopen($new_file,"w");			 if(!$fp) die("Error creating file");

 

It will be used later on to store the fetched records.

 

$link_id=mysql_connect($db_host, $db_username,$db_pass);	if(!$link_id)  die("connection failed");

 

Hopefully you can add a more appropriate error handler.

 

Setting the current database:

 

$current_db=mysql_select_db($db_name,$link_id);					if(!$current_db) die("error connecting host");

Creating a HTML table

echo "  <table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">";		echo "<tr>";

The table will hold a form which will display the options ( i.e. category as in my example).

Your users will choose a value from the options given.

		echo "<td><form name=\"form1\" enctype=\"multipart/form-data\" method=\"post\"	action=\"$PHP_SELF\">";								echo "<p>Please select a category </p>";

Now selecting the records ( i.e. category) one after another from the category table and displaying it in a list box

 

echo "<select name=\"category\" size=\"1\">";										  $search_string="SELECT DISTINCT category FROM tbl_test";			  $result_of_search_string=mysql_query($search_string);			  			  while($data_search_string=mysql_fetch_row($result_of_search_string))  								{								echo "<option> $data_search_string[0] </option>";								} 						 echo "</select>";

A simple one line instruction for the user:

 

echo "Click here to start :";

echo "<input type=\"submit\" name=\"Submit\" value=\"search\">";			 echo "</form>";			 echo"</tr>";

The FORM ends at this point Note the action attribute of the FORM tag set to $PHP_SELF

So when the user clicks on the submit botton the same page reloads with a value saved in the $category variable.

Now we'll show the record based on the options chosen by the user.

 

 

echo "<tr><td><h2> Your records </h2></td></tr>";

 

 

 

Let us set few global variables.

global $records_per_page, $cur_page, $PHP_SELF,  $search_category, $category;

 

Checking if $category variable stotes any value or not, if it does not, we set it to default : General/ Or any value you prefer, but it must be the one of the values you have entered in the category field of your table.

 

if(empty($_POST["category"])) 		   $search_category="General";			    else	  $search_category=$_POST["category"];

Checking is done. Now we'll show the user, what value he wanted to see.

 

echo "You are searching for $search_category";

 

Now counting the number of records found in the database for that category:

 

$count_query="select count(*) from tbl_test where category = '$search_category' ";

$result = mysql_query($count_query);   	if(!$result) die("error");	  				$query_data = mysql_fetch_row($result);				$total_record = $query_data[0];				if(!$total_record) die('No Record Found!');

Now setting the page number.

$page_num = $cur_page + 1;

Now setting the maximum number of records to be displayed in one page:

 

$records_per_page=10;

 

You can use another form to allow the user to select the records per page as we have done for selecting the category from the table.

 

Now we are calculating the total number of pages.

$total_num_page = $last_page_num 				   = ceil($total_record/$records_per_page);

 

 

echo "<tr><td><CENTER><H3>$total_record found on $search_category - Displaying the page					 $page_num out of $last_page_num.</H3></CENTER>\n</tr></td>";

 

Now set the current page number. To start with it will have a value = 0.

if(empty($cur_page)) 	 {	  $cur_page = 0;	   }

Now we set the limit for the records to be displayed per page.

 

$limit_str = "LIMIT ". $cur_page * $records_per_page . 									 ", $records_per_page";				$new_query="SELECT description FROM tbl_test where category LIKE '$search_category'";   			   $query=$new_query ." ". $limit_str;

 

Again a database query to fetch the records as per the option selected by the user. And display the records in a table.

 

$result = mysql_query($query);   		  if(!$result) die("No record found");				 while($data=mysql_fetch_row($result))  	  {  			 echo " <tr><td> $data[0] </td></tr> ";	  // this will show records		 fwrite($fp,$data[0]);			 // this will write to the file		 fwrite($fp,",");			 // This is the ", " seperater	 	 }

We have finished fetching records and at the same time stroring it in a file.

Now we are creating links for the user to nevigate to continuation pages.

 

if($page_num > 1) 	{	  $prev_page = $cur_page - 1;	  echo "<A HREF=\"$PHP_SELF?&cur_page=0\">[Top]</A>";	  echo "<A HREF=\"$PHP_SELF?&cur_page=$prev_page\">[Prev]</A> ";   }   if($page_num <  $total_num_page)    {	  $next_page = $cur_page + 1;	  $last_page = $total_num_page - 1;	  echo "<A HREF=\"$PHP_SELF?&cur_page=$next_page\">[Next]</A> ";	  echo "<A HREF=\"$PHP_SELF?&cur_page=$last_page\">[Bottom]</A>";   }

Now we'll allow the user to download the file.

 

echo "<tr><td><a href =\"$new_file\">Click here to download</a>";

Our task is over. Now some closing HTML tags again.

echo" </Td>";  echo " </tr>";  echo "</table>"; ?>

So far as the present script is concerned, I got a great help from a book entitle BEGINNING PHP4 published by Wrox Press Ltd. Infact page transition part has actually been scripted in that great book. It works nicely.

 

Regards,

Sid

[Quatrux 4]

But, how do I get the user to download this file? I want it to immediatly open the download window saying "Do you want to download this file" as it does in firefox, rather than take him to another page with a link to the file.
Any ideas?

Thanks.

To force the download is quite easy, you just need to send the right headers to the browsers

		header('Content-Encoding: none');			header('(anti-spam-content-type:) application/force-download');			header('Content-Disposition: attachment; filename="'.$file.'.csv"');			header('Cache-Control: must-revalidate, post-check="0", pre-check="0"');			header('Content-Length: '.filesize($file.'.csv'));			/* Output the File to User */			set_time_limit(0);			readfile($file.'.csv'); 			exit;

Just fix the variables you need, like if it is not going to be a file, don't use filesize() instead use strlen(), Well I think you only needed to know the Content-type of the force download, as I remember it works on all browsers I tested, except for old versions of IE. Be careful if you are using output_buffering, before the headers always do a ob_end_clean(); if you do.

[abhiram 0]

Hey Sid,Whew ... took quite some time to digest everything you said. You've infact given the entire framework, in astonishing accuracy, of exactly what I've done so far minus the file part. Thanks for taking the time to reply in such detail. I appreciate it.Now, you mean to say that it should output the records both, to the browser and also to a file simulataneously. Wouldn't this put unnecessary load on the server? The person using the system will definitely not need to download every page that he views and there are about 5000 records in all. If he chooses to 'view all' records, it'll take more time to display it since it's printing it to 2 places.One way to do what I want is to maybe have a link that passes the exact database query that the current page uses to another php page, which will simply write everything into a file and present a link on the next page for the user to download. Then I can have another link that'll take him back to the page he was viewing. Or maybe I can just open another browser window and give him the link so that he can just close the window to go back to the previous one.But, that's not what I would 'like' to do. I want a link or a form button on every page which when clicked, will send the output to a file and throw the file to the user, WITHOUT any change in what's being shown on the screen. I hope you get the point.@Quatrux:Thanks, but I want the user to be able to get the file without changing the current screen. Obviously, headers work only if used in the beginning of a particular page which means providing a link to another page and that's not exactly what I'm looking for.

[Quatrux 4]

Err, you will push the button to start the download and your page won't change, you'll see the same page and a window will appear which will say save/open :oWell, you could make it with AJAX, but I am still not good at it, just played with the forms, don't know much javascript, but you could send an xml request which also would force the download by the same headers I think.That is why I am using output buffering to have no problems with headers, sessions and cookies etc. ob_start() is great, besides you can control a lot of output too.

[iGuest 3]

..... I want a link or a form button on every page which when clicked, will send the output to a file and throw the file to the user, WITHOUT any change in what's being shown on the screen.

Try this one line Javascript code. Place this code at the end of dynamically generated pages.( i.e., once the WHILE LOOP for fetching record is complete)

<a href="#" onClick="window.open('/downloader.php?query=<?php echo $sql?>','mywindow','width=400,height=350,left=300,top=300,screenX=300,screenY=300')">Down load CSV!</a>

To run this Javascript code accurately, just copy and paste the code as it appears here. Otherwise, you will have to waste too much time in getting the required output just like me!

This will open a new window containing the page downloader.php and a query string $query which will contain the query to be executed later on.

 

In the downloader.php file, just get the required query using:

$query=$_GET["query"];

Now connect to the Database as before. Fetch required records using $query, put it in the CSV file ( i.e. $new_file, as mentioned earlier) .

Finally, in the same downloader.php file, put a line of code which will allow your visitor to download the selected records in CSV format.

 

echo "<a href =\"$new_file\">Click here to download</a>";

 

Please let me know whether it worked for you or not.

Regards,

Sid

[abhiram 0]

Well, that's very clear explanation. Thanks, I think I'm gonna use this method. I didnt' want the popup window actually, but I guess it's the simplest way to get it done without killing too many brain cells .I'm a little tied up right now, but, the moment I find time, I'll implement this and let you know how it works out.Thanks .