0 Replies Latest reply: Feb 1, 2011 3:42 AM by Wookie RSS

    Export search results to excel file (php)

    Wookie Community Member

      I've got a search results page and I want to have a button on that page that when clicked exports the search results as a CSV file.

       

      I've managed to get the button to work using this javascript:

       

      <script>
      function getcsv(){
      window.location="getcsv.php";
      }
      </script>
      <input type="button" onclick="getcsv()" value="Get a CSV File!">

       

       

      This is the code for 'getcsv.php' below.

      I understand that the code below the line highlighted in orange needs to be modified. At the moment it is just pulling the data from the 'install_tbl' (see line highlighted in red)

       

      How do I change the code so it creates the excel file from the search results instead?

       

       

       

      <?php require_once('Connections/conn_OSR.php'); ?>
      <?php
      if (!function_exists("GetSQLValueString")) {
      function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
      {
        $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

        $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

        switch ($theType) {
          case "text":
            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
            break;   
          case "long":
          case "int":
            $theValue = ($theValue != "") ? intval($theValue) : "NULL";
            break;
          case "double":
            $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
            break;
          case "date":
            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
            break;
          case "defined":
            $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
            break;
        }
        return $theValue;
      }
      }

      $colname2_rsSearchResults = "-1";
      if (isset($_GET['site_name'])) {
        $colname2_rsSearchResults = $_GET['site_name'];
      }
      $colname4_rsSearchResults = "-1";
      if (isset($_GET['project'])) {
        $colname4_rsSearchResults = $_GET['project'];
      }
      $colname3_rsSearchResults = "-1";
      if (isset($_GET['job_no'])) {
        $colname3_rsSearchResults = $_GET['job_no'];
      }
      $colname_rsSearchResults = "-1";
      if (isset($_SESSION['MM_Username'])) {
        $colname_rsSearchResults = $_SESSION['MM_Username'];
      }
      mysql_select_db($database_conn_OSR, $conn_OSR);
      $query_rsSearchResults = sprintf("SELECT * FROM joinversion5 WHERE site_name = %s AND username = %s OR job_no = %s AND username = %s OR project LIKE %s AND username = %s", GetSQLValueString($colname2_rsSearchResults, "text"),GetSQLValueString($colname_rsSearchResults, "text"),GetSQLValueString($colname3_rsSearchResults, "text"),GetSQLValueString($colname_rsSearchResults, "text"),GetSQLValueString("%" . $colname4_rsSearchResults . "%", "text"),GetSQLValueString($colname_rsSearchResults, "text"));
      $rsSearchResults = mysql_query($query_rsSearchResults, $conn_OSR) or die(mysql_error());
      $row_rsSearchResults = mysql_fetch_assoc($rsSearchResults);
      $totalRows_rsSearchResults = mysql_num_rows($rsSearchResults);


      // DB Connection here

      $select = "SELECT * FROM install_tbl";

      $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

      $fields = mysql_num_fields ( $export );

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

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

      if ( $data == "" )
      {
      $data = "\n(0) Records Found!\n";
      }

      header("Content-type: application/octet-stream");
      header("Content-Disposition: attachment; filename=your_desired_name.xls");
      header("Pragma: no-cache");
      header("Expires: 0");
      print "$header\n$data";

      mysql_free_result($rsSearchResults);
      ?>