PHP MySQL - Create CSV/XLS
Moderator: General Moderators
PHP MySQL - Create CSV/XLS
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?
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?
-
webmonkey88
- Forum Newbie
- Posts: 20
- Joined: Fri Aug 14, 2009 4:30 am
Re: PHP MySQL - Create CSV/XLS
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
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
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
-
webmonkey88
- Forum Newbie
- Posts: 20
- Joined: Fri Aug 14, 2009 4:30 am
Re: PHP MySQL - Create CSV/XLS
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
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
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?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);
-
webmonkey88
- Forum Newbie
- Posts: 20
- Joined: Fri Aug 14, 2009 4:30 am
Re: PHP MySQL - Create CSV/XLS
you would loop through the result set and for each row add a new line
Re: PHP MySQL - Create CSV/XLS
sorry how would I do that?
curretly I have this as my query and display:
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).
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();
?>
(I have the color 1, and color 2 so that it has alternating line colours but obviously I dont need this in a CSV).
-
webmonkey88
- Forum Newbie
- Posts: 20
- Joined: Fri Aug 14, 2009 4:30 am
Re: PHP MySQL - Create CSV/XLS
within the while you would do the following:
basically for each row you will add a row in the csv file.
Code: Select all
$csv_text .= "\"," . $rows['col1'] . "\",\" . ect ect
Re: PHP MySQL - Create CSV/XLS
Great!!
Thanks. This works a treat.
I have used this code:
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.
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();
?>Just 1 question.
Is there a way of adding 1 row at the start of the csv as titles as this is not included.