MySql to .xls php script help

[rattlsnak]

I have a MySql database that I need to be able to send to an .xls file that has multiple rows and columns. I would like to be able to generate a php script that will send a copy of the database to excel preferably from a onclick function so as the user can simply click a button to do it. I've looked at a few codes online, but everyone has a completely different way of doing it, so wondering if anybody here has a preferred way.

Thanks!
Marc

[stomachmonkey]

phpMyAdmin will export to CSV pretty much one click.

But users need access to it.

Set permissions properly and it should be ok.

Is the export an entire DB or the results of a query?

[rattlsnak]

I setup up the database through phpmyadmin but the users will only have access to a website. Right now, I have setup a query based on a gui format where it can be searched and information entered by the users but also want an .xls file that I will use on google docs to share with other board members. I need it to be updated once a week or so hopefully from a oneclick button from one of the users. It will be the entire database each time, unless their is a way to update on the last few lines and export that to be added to the existing .xls?

In a nutshell, this is a database in which we allow a few users to enter penalties (local hockey club) into that can be later searched by the league officers and they want a local copy in an .xls format. So the users entering simply would have no clue or permissions to the database for anything else except data entry.

[id10t]

Something like this should work. Took it from an existing setup I was using, removed the logic that specified some control over which rows were sent so it sends everything matching your query. Note that mysql_* functions are on the way out and should be replaced with PDO object calls instead.... If you can send me a mysql_dump of the table and the query you'd want to use (assuming you just don't want everything) and I'll do it up and debug it, then send you back the known-good PHP. Email it to me - sj at gruv dot org

PHP Code:

<?php

// written by id10t licensed under the GPLv2
// should be updated to use PDO 


    // the query your csv file will be based on
    $query="select foo,bar,bee from somedbtable";

// what database is your table stored in
    $dbName="your-database-name";

// your database host - get from ISP or your geek
    $hostname="mysqlserver.example.com";

// db user credentials - get from ISP or your geek
    $username="dbuser";
    $password="secretwords";

// nothing to edit below this line ... unless you want to 

    // send response headers to the browser
    header( 'Content-Type: text/csv' );
    header( 'Content-Disposition: attachment;filename=SMART.csv');
    $fp = fopen('php://output', 'w');
    
    MYSQL_CONNECT($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");
    mysql_select_db($dbName) or DIE("DB unavailable");
    $result = mysql_query($query) or die($query);
    $number = mysql_num_rows($result);
    // output header row (if at least one row exists)
    $row = mysql_fetch_assoc($result);
    if($row) {
    fputcsv($fp, array_keys($row));
    // reset pointer back to beginning
    mysql_data_seek($result, 0);
    }
    for($i=0;$i<$number;$i++){
      $row = mysql_fetch_row($result);
      fputcsv($fp, $row);
    }
    mysql_close();
    fclose($fp);
?>

[rattlsnak]

Sent you an email.. Thanks!

[id10t]

Replied

[rattlsnak]

THANK YOU STEVE!! This board Rocks!!!

[id10t]

Code had a minor bug in it (extra end parens left over from a removed print function), I've fixed the copy above. Also added some variable declaration and comments at the top since that is what rattlsnak had questions about.

Licensed under GPLv2 - use, change, share, enjoy