Tuesday, November 24, 2009

Fetch record export to excel in csv fromat in php

this example will help to fetch record from database and export it to excel file in csv format in php.


<?php

class database

{
private $db_handle;
private $user_name;
private $password;
private $data_base;
private $host_name;
private $sql;
private $results;

function __construct($host="localhost",$user,$passwd)

{
$this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)

{
$this->data_base = $db;
if(!mysql_select_db($this->data_base, $this->db_handle))
{
error_log(mysql_error(), 3, "/phplog.err");
die("Error connecting to Database");
}
}

function executeSql($sql_stmt)
{
$this->sql = $sql_stmt;
$this->result = mysql_query($this->sql);
}
function returnResults()
{
return $this->result;
}
}


$dbc=mysql_connect('localhost','username','password');
$db_selected =mysql_select_db('databasename',$dbc);

$user = "username";

$passwd = "password";
$db = "database name";

$videoCount="select * from users ";



$dbObject = new database($host,$user,$passwd);

$dbObject->dbSelect($db);
$dbObject->executeSql($videoCount);

$res = $dbObject->returnResults();

$file = "excel.csv";

$nameStr = "";

function getUserName($id,$dbc){


$getUserName="select user_id,user_name,dept from users where user_id=$id";
$userNameResult=mysql_query($getUserName,$dbc)or ('Error in get User Name'.mysql_error());
$name=mysql_fetch_row($userNameResult);

return($name);

}


$Title.=$Title."User Name".","."Department"."\n";

while($record = mysql_fetch_object($res))

{

$vTitle = $record->video_title;
$usrName=getUserName($record->user_id,$dbc);



$nameStr = $nameStr.$vTitle.",".$usrName[0].",".$usrName[1]."\n";

}

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=$file");
header("Cache-Control: public");
header("Content-length: ".strlen($nameStr)); // tells file size
header("Pragma: no-cache");
header("Expires: 0");
echo $Title;
echo $nameStr;

?>

No comments:

Post a Comment