PHP MySQL - Create CSV/XLS

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

PHP MySQL - Create CSV/XLS

Post 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?
webmonkey88
Forum Newbie
Posts: 20
Joined: Fri Aug 14, 2009 4:30 am

Re: PHP MySQL - Create CSV/XLS

Post 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/
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

Re: PHP MySQL - Create CSV/XLS

Post 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
webmonkey88
Forum Newbie
Posts: 20
Joined: Fri Aug 14, 2009 4:30 am

Re: PHP MySQL - Create CSV/XLS

Post 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);
 
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

Re: PHP MySQL - Create CSV/XLS

Post 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.
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

Re: PHP MySQL - Create CSV/XLS

Post 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?
webmonkey88
Forum Newbie
Posts: 20
Joined: Fri Aug 14, 2009 4:30 am

Re: PHP MySQL - Create CSV/XLS

Post by webmonkey88 »

you would loop through the result set and for each row add a new line
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

Re: PHP MySQL - Create CSV/XLS

Post 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).
webmonkey88
Forum Newbie
Posts: 20
Joined: Fri Aug 14, 2009 4:30 am

Re: PHP MySQL - Create CSV/XLS

Post 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.
DAVID87
Forum Commoner
Posts: 29
Joined: Fri Jun 26, 2009 9:56 am
Location: Nottingham, UK

Re: PHP MySQL - Create CSV/XLS

Post 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.
Post Reply