Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting SQL to a XLS file on the Local server for email.

    I already have the export to excel working, its awesome but the bad part is I can't get it to save on the server. I have to invoke the download in a browser and then I need to be able to echo a Y for save and type the location. Here is the script i currently use.

    Anything you can do to help me would be great! Thanks!

    <?php
    // Edit the $Host, $User $Password, $DBName and $TableName vars only! //
    $Host = "localhost";
    $User = "user";
    $Password = "password";
    $DBName = "database";
    $TableName = "table";

    // DO NOT EDIT BELOW HERE //
    $link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
    mysql_select_db($DBName) or die('Could not select database');

    $select = "SELECT * FROM `".$TableName."`";
    $export = mysql_query($select);
    $fields = mysql_num_fields($export);

    for ($i = 0; $i < $fields; $i++) {
    $csv_output .= mysql_field_name($export, $i) . "\t";
    }

    while($row = mysql_fetch_row($export)) {
    $line = '';
    foreach($row as $value) {
    if ((!isset($value)) OR ($value == "")) {
    $value = "\t";
    } else {
    $value = str_replace('"', '""', $value);
    $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
    }
    $data .= trim($line)."\n";
    }
    $data = str_replace("\r","",$data);

    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=sql_dump.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    readfile("sql_dump.xls");
    exit;
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <title>Download MySQL Table Code</title>
    </head>
    <body>

    </body>
    </html>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Won't get it on the server that way.

    That is strictly for causing MS Excel to display the data in a spreadsheet from the browser.

    If you want to get the data into Excel on the server, you'll have to script the Excel object, and I doubt whether PHP will do that. Other option might be to connect to Excel using the JET OLE DB driver, if PHP will make that connection (it should).

  • #3
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    If you want to get the data into Excel on the server, you'll have to script the Excel object, and I doubt whether PHP will do that. Other option might be to connect to Excel using the JET OLE DB driver, if PHP will make that connection (it should).
    I am fairly new to MySQL and know enough php to get by. I am not sure I understand what JET OLE DB is. Sorry for sounding dumb. :-\

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    JET OLE DB is the Microsoft driver that allows you to treat various kinds of text files (e.g., ".csv" and tab-delimited) AND ".xls" (excel) files as if they were an Access-style database.

    It will only work on a Windows machine, of course. Has nothing to do with MySQL. You would need a separate connection to that driver from PHP. I've never worked with PHP and JET together, but I have to assume there is a way to do so.

  • #5
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    "Your issue looks interesting. I want to help you with a method of data export. If you want to export data from database, you may choose it, it's very fast and stable when you export data from database. And now, it's free for everyone. You may visit here for further information:
    http://www.codeproject.com/KB/cs/Excel_PDF_Word_ExportWiz.aspx"


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •