exporting query results to Excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • alf8kitty
    New Member
    • Feb 2008
    • 6

    exporting query results to Excel

    Hello,
    Im still very new to php and am having a problem.
    I return a MySQL query to a form in my php page and I want to be able to export the form data to Excel when the users clicks a link (or a button).
    I found some code and can apparently export the entire contents of a table in my database, but when i try to limit the export result using variables...i get nothing. I am hoping someone can point me in the right direction?

    This is the query that returns the correct results to the form.
    [PHP]$query="SELECT ts.TimeSheetID, ts.TaskDT, e.UserName, c.LastName, ts.HoursWorked, ts.Notes
    FROM Emp_TimeSheet ts INNER JOIN Employee e ON ts.EmployeeID=e .EmployeeID
    INNER JOIN Client c ON ts.ClientID=c.C lientID
    WHERE e.EmployeeID IN ($select_ids) AND (ts.TaskDT >= '$Date' AND ts.TaskDT <='$Date2') ORDER BY ts.TaskDT, e.UserName";[/PHP]
    In the same page I tried to save the variables to SESSION by:
    [PHP]session_start() ;
    // to change a variable, just overwrite it
    $_SESSION['startDate']=$Date;
    $_SESSION['endDate']=$Date2;
    $_SESSION['select_ids']=$select_ids;
    $_SESSION['querystring']=$query;
    session_write_c lose();[/PHP]

    There is a link on this same page that calls:mysql-php-xls.php (see code below)
    I can export the results when I hard code in the EmployeeIDs and the dates, but I need to get the variables (passed through from the previous page to work).

    [PHP]<?php
    $Host = "localhost" ;
    $User = "user";
    $Password = "pswd";
    $DBName = "TimeSheet" ;
    $TableName = "Emp_TimeSheet" ;

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

    $startDT=$_SESS ION['startDate'];
    $endDT=$_SESSIO N['endDate'];
    $Employees=$_SE SSION['select_ids'];
    $querystring=$_ SESSION['querystring'];

    //$select = '$querystring';
    $select = "SELECT et.TaskDT, e.UserName, et.HoursWorked, et.Notes FROM Emp_TimeSheet et INNER JOIN Employee e ON et.EmployeeID=e .EmployeeID WHERE EmployeeID IN ($Employees) AND et.TaskDT >= $startDT AND et.TaskDT<=$end DT ORDER BY et.TaskDT, et.EmployeeID";
    $export = mysql_query($se lect);
    $fields = mysql_num_field s($export);

    for ($i = 0; $i < $fields; $i++) {
    $csv_output .= mysql_field_nam e($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=databa se_dump.xls");
    header("Pragma: no-cache");
    header("Expires : 0");
    print $csv_output."\n ".$data;
    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>Downlo ad MySQL Table Code</title>
    </head>
    <body>

    </body>
    </html>[/PHP]
  • ronverdonk
    Recognized Expert Specialist
    • Jul 2006
    • 4259

    #2
    I cannot see it, but you must start script 'mysql-php-xls.php' with the session_start() right at the top, otherwise you cannot access the $_SESISON variables you stored there in the calling script.

    Ronald

    Comment

    • harshmaul
      Recognized Expert Contributor
      • Jul 2007
      • 490

      #3
      Hi,
      sorry i had problems with my connection. it was really fustrating cos i had a solution but couldn't post it!!!

      any way, the following will generate a excel file....


      [PHP]<?php
      header("Content-Type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=databa se_dump.xls");
      ?>
      <!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>Downlo ad MySQL Table Code</title>
      </head>
      <body>
      <table>
      <tr>
      <td>1</td>
      <td>2</td>
      <td>3</td>
      <td>4</td>
      </tr>
      <tr>
      <td>q</td>
      <td>w</td>
      <td>e</td>
      <td>r</td>
      </tr>

      </table>
      </body>
      </html>[/PHP]


      If you populate the HTML table into a proper table with the data how you want it to look in excel it should work fine.

      hope that helps

      Comment

      • eBay
        New Member
        • Mar 2008
        • 12

        #4
        Here's an example of how it can be done from an MSKB article. I just tried this page but it only runs from IE (because of ActiveX) and you must have Excel installed on the client machine.

        Generated Excel Content

        I don't like this solution myself because it's too browser and product dependent. There were some slick Javascript classes that did graphs written by some guy at Netscape years ago. In hindsight, that approach was the most viable and long lasting and I prefer it because the data prep is done at the server and the graphing is done at the client.

        Comment

        • alf8kitty
          New Member
          • Feb 2008
          • 6

          #5
          cool thanks! ill try em out.

          Comment

          • alf8kitty
            New Member
            • Feb 2008
            • 6

            #6
            i can get the export to work (thanks!) but is there a way to call it through a button click? it would be nice to be able to display the table first and allow the user the choice to export or not.

            Comment

            • eBay
              New Member
              • Mar 2008
              • 12

              #7
              Actually, I got side tracked by the title. The title probably should have been--

              Having Problems with MySQL Where Clause

              The error is on line 17 of the third block of code:

              $startDT AND et.TaskDT<=$end DT

              The PHP variables $startDT and $endDT are not surrounded by single quotation marks.

              I think this should solve it.

              Comment

              • Markus
                Recognized Expert Expert
                • Jun 2007
                • 6092

                #8
                Originally posted by eBay
                Actually, I got side tracked by the title. The title probably should have been--

                Having Problems with MySQL Where Clause

                The error is on line 17 of the third block of code:

                $startDT AND et.TaskDT<=$end DT

                The PHP variables $startDT and $endDT are not surrounded by single quotation marks.

                I think this should solve it.
                Clearly this isn't the problem as the OP wouldn't have thanked hsriat for providing a code that got the
                Originally posted by alf8kitty
                export to work (thanks!)

                Comment

                Working...