Page 1 of 1

Output to excel

Posted: Tue Mar 24, 2009 9:45 am
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?

Re: Output to excel

Posted: Tue Mar 24, 2009 12:35 pm
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.

Re: Output to excel

Posted: Tue Mar 24, 2009 2:30 pm
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.

Re: Output to excel

Posted: Tue Mar 24, 2009 4:35 pm
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