Page 1 of 1

PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 8:31 am
by DAVID87
Hi All,

I am trying to find out if this is possible and if so some snippets of code so that I can write my script properly.

What I am trying/wanting to do is have a button/link on my site that a user can click to download a created csv/xls file.

I have a SELECT query running on my page that is then formatted so that the data can be seen in a nice way that suits my site. However below all of this displayed data I would like a link so that it can be saved by the user to a CSV/XLS file on their machine (basically pulls the Select query and downloads it to the users machine in a CSV/XLS file).

Can anyone help/point me in the correct direction?

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:02 am
by webmonkey88
you can create a csv file of the data by using the fopen() fwrite() and sending it your data in the csv format(comma separated) or there are some PEAR modules that create a xls file http://pear.php.net/package/Spreadsheet_Excel_Writer/

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:09 am
by DAVID87
Thats great,

However I have no idea on how to use fopen and fwrite. I will try and do some reasearch into those now though. As for the Excel Writer, I donot have access to install anything on my hosting server, so I will not be able to install that, however CSV format shopuld be fine if it is the easiest way.

Thanks for the reply

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:17 am
by webmonkey88
Ye creating a csv is the easiest way of doing it, along the lines of the following:

Code: Select all

 
$csv_file ="mycsv.csv";
$csv_text = "
                   \"1\",\"2\",\"3\",\"4\",\"5\"\n
                   \"1\",\"2\",\"3\",\"4\",\"5\"\n
                ";
  
$csv_handle = fopen($csv_file,"w");
fwrite($csv_handle, $csv_text);
fclose($csv_handle);
 

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:19 am
by DAVID87
sorry. miss read the information on that website. I hought with it saying "install" I actually had to install an application and not just the php code sheets. I will look at this as there is an extensive tutorial on the website. thanks again for the reply.

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:21 am
by DAVID87
webmonkey88 wrote:Ye creating a csv is the easiest way of doing it, along the lines of the following:

Code: Select all

 
$csv_file ="mycsv.csv";
$csv_text = "
                   \"1\",\"2\",\"3\",\"4\",\"5\"\n
                   \"1\",\"2\",\"3\",\"4\",\"5\"\n
                ";
  
$csv_handle = fopen($csv_file,"w");
fwrite($csv_handle, $csv_text);
fclose($csv_handle);
 
Thanks for this code. seems simple enough but would I put the query in the $csv_text or would i run the query first and then just the results in the $csv_text area?

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:25 am
by webmonkey88
you would loop through the result set and for each row add a new line

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:37 am
by DAVID87
sorry how would I do that?

curretly I have this as my query and display:

Code: Select all

 
<?php
include 'dbc.php';
$sql="SELECT * FROM membership WHERE member = 'Y' ORDER BY DOB DESC";
$result = mysql_query($sql);
while($rows=mysql_fetch_array($result)){
if($color==1){
echo "....."
$color="2";
} else {
echo "....."
$color="1";
}}
mysql_close();
?>
 
The .....'s on the echo is basically just formatting and displaying the results in the form of $rows['column']

(I have the color 1, and color 2 so that it has alternating line colours but obviously I dont need this in a CSV).

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 9:53 am
by webmonkey88
within the while you would do the following:

Code: Select all

 
$csv_text .= "\"," . $rows['col1'] . "\",\" . ect ect
 
basically for each row you will add a row in the csv file.

Re: PHP MySQL - Create CSV/XLS

Posted: Fri Aug 14, 2009 10:21 am
by DAVID87
Great!!

Thanks. This works a treat.

I have used this code:

Code: Select all

<?php
$csv_file ="mycsv.csv";
include 'dbc.php';
$sql="SELECT * FROM membership WHERE member = 'Y' ORDER BY DOB DESC";
$result = mysql_query($sql);
while($rows=mysql_fetch_array($result)){
$csv_text .= "\"".$rows['full_name']."\"\n" ;
}
$csv_handle = fopen($csv_file,"w");
fwrite($csv_handle, $csv_text);
fclose($csv_handle);
mysql_close();
?>
and get my csv file with just a list of full names (just put that on there for now as a test.)

Just 1 question.

Is there a way of adding 1 row at the start of the csv as titles as this is not included.