Output to excel

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mrjtfool
Forum Newbie
Posts: 11
Joined: Fri Feb 06, 2009 4:36 pm

Output to excel

Post by mrjtfool »

Does anyone know of an easy way to output a mysql query result to a spreadhseet.

Currently I am using a bit of code:

Code: Select all

<?php
$mysqli = mysqli_connect("localhost", "my_user", "my_pass", "my_db");
$sql = "SELECT * FROM salesperson INTO OUTFILE 'test.csv';
$res = mysqli_query($mysqli, $sql)
                    or die(mysqli_error($mysqli));
?>
that simply creates a .csv file which can be opened. However when you open the file all the lines of data from the database are bunched up in one cell on the left hand side.

Is there any way the data can be seperated into different cells and also is there a way of adding a field name to the top of the columns?
waylon999
Forum Commoner
Posts: 26
Joined: Mon Mar 23, 2009 5:29 pm

Re: Output to excel

Post by waylon999 »

I'm not sure of any way to do it all with a mysql statement, but you could take the results of a query and manually separate them by comma.

Code: Select all

 
$fout = fopen('test.csv','wb') or die ('Cannot open the file for output');
//write the csv header row
fwrite($fout,"id, name, address\n");
 
$query = 'SELECT id, name, address FROM salesperson';
$result = mysql_query($query);
 
while($row = mysql_fetch_assoc($result)){
   fwrite($fout,$data['id'].",".$data['name'].",".$data['address']."\n");
}
fclose($fout);
 
Should be something like that.
mrjtfool
Forum Newbie
Posts: 11
Joined: Fri Feb 06, 2009 4:36 pm

Re: Output to excel

Post by mrjtfool »

Hey thanks that worked really well. I've amended the code to go through all the locations in my database and list the salesman that are there but for some reason the spreadsheet is blank when I open it. The only thing that's on it is the headers (Branch, Salesman ID, First Name, Last Name) the rest is blank.

Code: Select all

<?php
$mysqli = mysqli_connect("localhost", "my_username", "my_pass", "my_database");
$i = 1;
for ($i=1; $i <= 23; $i++){
    $fout = fopen('test.csv','wb') or die ('Cannot open the file for output');
    fwrite($fout,"Branch, Salesman ID, First Name, Last Name\n");
    $sql = "SELECT branch.name, salesperson.salesman_no, salesperson.first_name, salesperson.last_name FROM branch 
            LEFT JOIN (salesperson) ON salesperson.branch_no = branch.branch_no 
            WHERE salesperson.branch_no = '$i'";
    $res = mysqli_query($mysqli, $sql);
    while($row = mysqli_fetch_assoc($res)){
        fwrite($fout,$row['name'].",".$row['salesman_no'].",".$row['first_name'].",".$row['last_name']."\n");
        }
fwrite($fout,"\n");
}
fclose($fout);
?>
I've tested and the variable $i increments up to 24 as it should. Also this code works when you change $i to an actual number such as "1" on line 9.

Not sure why this is happening?

**EDIT** I worked out why this wasn't working. Had to move the part where the file is opened out of the for loop.

Is there anyway of making the text bold or is that pushing the limit on php?

I've heard there is some php library that interacts with excel from php scripts very well but this seems a bit too confusing for someone as new to php as me.
waylon999
Forum Commoner
Posts: 26
Joined: Mon Mar 23, 2009 5:29 pm

Re: Output to excel

Post by waylon999 »

There is no formatting in csv files, but you could make an xls file with formatting. I think this would require an external package though like this:
http://pear.php.net/package/Spreadsheet_Excel_Writer
Post Reply