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
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('ExpirHi,
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>";
?>
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.