Member Avatar for Kparas

HI All,

I am working first time on CSV files. I want to fetch record or value which are coming from database in php. I want to fetch these all value in CSV file or excel spreadsheet. Please help me . How i can do this.Its very urgent for me.


Thanks,
Kparas

  • 4 Contributors
  • 3 Replies
  • 121 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Thamaraikani

Recommended Answers

All 3 Replies

Member Avatar for praveen_dusari

hi, check out this attachments, in dump_not1.php page edit the sql code and give a link to that page

<a href="dump_not1.php?type=1" class="slinks">Download To CSV </a>

you will get it

<?php
/**
 *  EXAMPLE (iam_dsvdump)
 *
 *  @author     Ivn Ariel Melgrati <phpclasses@imelgrat.mailshell.com>
 *  @version    1.0
 *  @package    iam_csvdump
 *
 *  A class form performing a query dump and sending it to the browser or setting it or download.
 *  Requires PHP v 4.0+ and MySQL 3.23+
 *
 *  Copyright (C) Ivn Ariel Melgrati <phpclasses@imelgrat.mailshell.com>
 *
 *  This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public
 *  License as published by the Free Software Foundation; either
 *  version 2 of the License, or (at your option) any later version.
 *
 *  This library is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  Lesser General Public License for more details.
 */


  #####################################################################################################################
  #                      Include the class                                                                            #
  #####################################################################################################################
  require_once("iam_csvdump.php");

  #####################################################################################################################
  #  Set the parameters: SQL Query, hostname, databasename, dbuser and password                                       #
  #####################################################################################################################
  $dumpfile = new iam_csvdump;

  #####################################################################################################################
  #  Call the CSV Dumping function and THAT'S IT!!!!  A file named dump.csv is sent to the user for download          #
  #####################################################################################################################
   if(!empty($_GET['type'])){
    $dumpfile->dump("SELECT r.p_id as Id,r.p_name as ProductName,r.p_quality as Quality,r.stock  as Stock,r.p_price as Price FROM `tbl_products` as r where r.status=1 ", "dump", "csv", "getthebe_Vegbest", "getthebe_Getbest", "C}71Z<l2@KK)", "localhost" );
   }
   
   //$dumpfile2->dump("SELECT r.salutation as Salutation,r.username as UserName, r.fname as firstname, r.lname as lastname ,r.email as Email ,r.customerid as customerid,r.organisation as Organisation,r.department as Department ,r.phone as phoneno,r.jobtitle as jobtitle ,r.city as city ,r.language as language ,r.state as state ,r.country as country ,r.pincode as pincode  ,r.pobox as pobox  ,r.fax as fax  FROM `bio_users` as r ", "dump", "csv", "bitrane_biogen", "bitrane_biox", "9bio99", "mysql19.ixwebhosting.com" );
  //$dumpfile->dump("SELECT * FROM `points_table_cal` as p,`temp_points` as t where p.p_id=t.pid_fk and points_total<=75", "dump", "csv", "opulentus", "root", "1234", "localhost" );

?>
<?php

/**
 *  IAM_CSVDump A class form performing a query dump and sending it to the browser or setting it or download.
 *  @package    iam_csvdump
 */

 /**
 *  IAM_CSVDump A class form performing a query dump and sending it to the browser or setting it or download.
 *  @author     Ivn Ariel Melgrati <phpclasses@imelgrat.mailshell.com>
 *  @package    iam_csvdump
 *  @version 1.0
 *
 *  IAM_CSVDump A class form performing a query dump and sending it to the browser or setting it or download.
 *
 *  Browser and OS detection for appropriate handling of download and EOL chars.
 *
 *  Requires PHP v 4.0+ and MySQL 3.23+. Some portions taken from the CSV_UTIL_CLASS by Andrej Arn <andrej@blueshoes.org>.
 *
 *  This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public
 *  License as published by the Free Software Foundation; either
 *  version 2 of the License, or (at your option) any later version.
 *
 *  This library is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  Lesser General Public License for more details.
 */
class iam_csvdump
{

    /**
    * @desc Takes an array and creates a csv string from it.
    *
    * @access public
    * @param  Array  $array (see below)
    * @param  String $separator Field separator ()default is ';')
    * @param  String $trim  If the cells should be trimmed , default is 'both'. It can also be 'left', 'right' or 'both'. 'none' makes it faster since omits many function calls.
    * @param  Boolean   $removeEmptyLines (default is TRUE. removes "lines" that have no value, would come out empty.)
    * @return String A CSV String. It returns an empty string if there Array is empty (NULL)
    * @todo Add param "fill to fit max length"?
    */
    function arrayToCsvString($array, $separator=';', $trim='both', $removeEmptyLines=TRUE) {
    if (!is_array($array) || empty($array)) return '';

    switch ($trim) {
      case 'none':
        $trimFunction = FALSE;
        break;
      case 'left':
        $trimFunction = 'ltrim';
        break;
      case 'right':
        $trimFunction = 'rtrim';
        break;
      default: //'both':
        $trimFunction = 'trim';
        break;
    }
    $ret = array();
    reset($array);
    if (is_array(current($array))) {
      while (list(,$lineArr) = each($array)) {
        if (!is_array($lineArr)) {
          //Could issue a warning ...
          $ret[] = array();
        } else {
          $subArr = array();
          while (list(,$val) = each($lineArr)) {
            $val      = $this->_valToCsvHelper($val, $separator, $trimFunction);
            $subArr[] = $val;
          }
        }
        $ret[] = join($separator, $subArr);
      }
     $crlf = $this->_define_newline();
     return join($crlf, $ret);
    } else {
      while (list(,$val) = each($array)) {
        $val   = $this->_valToCsvHelper($val, $separator, $trimFunction);
        $ret[] = $val;
      }
      return join($separator, $ret);
    }
    }

    /**
    * @desc Works on a string to include in a csv string.
    * @access private
    * @param  String $val
    * @param  String $separator
    * @param  Mixed  $trimFunction If the cells should be trimmed , default is 'both'. It can also be 'left', 'right' or 'both'. 'none' makes it faster since omits many function calls.
    * @return String
    * @see    arrayToCsvString()
    */
    function _valToCsvHelper($val, $separator, $trimFunction) {
    if ($trimFunction) $val = $trimFunction($val);
    //If there is a separator (;) or a quote (") or a linebreak in the string, we need to quote it.
    $needQuote = FALSE;
    do {
      if (strpos($val, '"') !== FALSE) {
        $val = str_replace('"', '""', $val);
        $needQuote = TRUE;
        break;
      }
      if (strpos($val, $separator) !== FALSE) {
        $needQuote = TRUE;
        break;
      }
      if ((strpos($val, "\n") !== FALSE) || (strpos($val, "\r") !== FALSE)) { // \r is for mac
        $needQuote = TRUE;
        break;
      }
    } while (FALSE);
    if ($needQuote) {
      $val = '"' . $val . '"';
    }
    return $val;
    }

    /**
    * @desc Define EOL character according to target OS
    * @access private
    * @return String A String containing the End Of Line Sequence corresponding to the client's OS
    */
    function _define_newline()
    {
         $unewline = "\r\n";

         if (strstr(strtolower($_SERVER["HTTP_USER_AGENT"]), 'win'))
         {
            $unewline = "\r\n";
         }
         else if (strstr(strtolower($_SERVER["HTTP_USER_AGENT"]), 'mac'))
         {
            $unewline = "\r";
         }
         else
         {
            $unewline = "\n";
         }

         return $unewline;
    }

    /**
    * @desc Define the client's browser type
    * @access private
    * @return String A String containing the Browser's type or brand
    */
    function _get_browser_type()
    {
        $USER_BROWSER_AGENT="";

        if (ereg('OPERA(/| )([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version))
        {
            $USER_BROWSER_AGENT='OPERA';
        }
        else if (ereg('MSIE ([0-9].[0-9]{1,2})',strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version))
        {
            $USER_BROWSER_AGENT='IE';
        }
        else if (ereg('OMNIWEB/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version))
        {
            $USER_BROWSER_AGENT='OMNIWEB';
        }
        else if (ereg('MOZILLA/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version))
        {
            $USER_BROWSER_AGENT='MOZILLA';
        }
        else if (ereg('KONQUEROR/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version))
        {
            $USER_BROWSER_AGENT='KONQUEROR';
        }
        else
        {
            $USER_BROWSER_AGENT='OTHER';
        }

        return $USER_BROWSER_AGENT;
    }

    /**
    * @desc Define MIME-TYPE according to target Browser
    * @access private
    * @return String A string containing the MIME-TYPE String corresponding to the client's browser
    */
    function _get_mime_type()
    {
        $USER_BROWSER_AGENT= $this->_get_browser_type();

        $mime_type = ($USER_BROWSER_AGENT == 'IE' || $USER_BROWSER_AGENT == 'OPERA')
                       ? 'application/octetstream'
                       : 'application/octet-stream';
        return $mime_type;
    }

    /**
    * @desc Generates a CSV File from an SQL String (and outputs it to the browser)
    * @access private
    * @param  String $dbname Name of the Database
    * @param  String $user User to Access the Database
    * @param  String $password Password to Access the Database
    * @param  String $host Name of the Host holding the DB
    */
    function _db_connect($dbname="mysql", $user="root", $password="", $host="localhost")
    {
      $result = @mysql_pconnect($host, $user, $password);
      if(!$result)     // If no connection, return 0
      {
       return false;
      }

      if(!@mysql_select_db($dbname))  // If db not set, return 0
      {
       return false;
      }
      return $result;
    }

    /**
    * @desc Generates a CSV File from an SQL String (and outputs it to the browser)
    * @access private
    * @param  String $query_string An SQL statement (usually a SELECT statement)
    * @param  String $dbname Name of the Database
    * @param  String $user User to Access the Database
    * @param  String $password Password to Access the Database
    * @param  String $host Name of the Host holding the DB
    * @param  Boolean $list_fields Whether to output the field names in the first row
    */
    function _generate_csv($query_string, $dbname="mysql", $user="root", $password="", $host="localhost", $list_fields=true)
    {
      if(!$conn= $this->_db_connect($dbname, $user , $password, $host))
          die("Error. Cannot connect to Database.");
      else
      {
        $result = @mysql_query($query_string, $conn);
        if(!$result)
            die("Could not perform the Query: ".mysql_error());
        else
        {
            $file = "";
            $crlf = $this->_define_newline();
            if($list_fields)
            {
                 for($i=0;$i < (mysql_num_fields($result))-1;$i++)
                    $file.= mysql_field_name($result,$i).",";
                 $file.=mysql_field_name($result,mysql_num_fields($result)-1).$crlf;
            }

            while ($str= @mysql_fetch_array($result, MYSQL_NUM))
            {
                $file .= $this->arrayToCsvString($str,",").$crlf;
            }
            echo $file;
        }
      }
    }

    /**
    * @desc Generate the CSV File and send it to browser or download it as a file
    * @access public
    * @param String $query_string  An SQL statement (usually a SELECT statement)
    * @param String $filename  Filename to use when downloading the File. Default="dump". If set to "", the dump is displayed on the browser.
    * @param String $extension Extension to use when downloading the File. Default="csv"
    * @param  String $dbname Name of the Database to use
    * @param  String $user User to Access the Database
    * @param  String $password Password to Access the Database
    * @param  String $host Name of the Host holding the DB
    * @param  Boolean $list_fields Whether to output the field names in the first row
    */
    function dump($query_string, $filename="useres", $ext="csv", $dbname="mysql", $user="root", $password="", $host="localhost", $list_fields=true )
    {
            $now = gmdate('D, d M Y H:i:s') . ' GMT';
            $USER_BROWSER_AGENT= $this->_get_browser_type();

            if ($filename!="")
            {
                 header('Content-Type: ' . $this->_get_mime_type());
                 header('Expir
Member Avatar for Thamaraikani

Hi,
This is the code that i used for converting .csv to .xls...I think this may help you.

<?php
header("Content-type:application/ms-excel");
header("Content-Disposition:attachment;filename='csv to excel'");
?>
<?php
    $open = fopen("phpex1.csv", "a+"); //open the csv file
    echo "<table border='1'>";
    echo "<tr><td colspan='15'><h1><center>.csv to .xls</center></h1></td></tr>";
    while (($data = fgetcsv($open," ", ",")) !== FALSE) 
        {
            $num = count($data); //get number of data in a line
            echo "<tr>";
                for ($c=0; $c < $num; $c++){
                    echo "<td>";     
                    echo $data[$c]; // data that are get entered into the table
                    echo "</td>";
                }
            echo "</tr>";
        }
    fclose($open); //close the file
    echo "</table>";
?>
Edited by Nick Evan because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.