beshoo
Posts: 68
|
| Posted: 12/12/2009, 2:15 PM |
|
All of us have this challenge to export your report to csv file , the old solution is to export al hall page with cleaning the CSS and images and make the record limits to 5000 record so you can assure that this is the full records that your clients needs.
Well I don’t like the headache . so I invent an new method to do that which is very simple to make and very handy .
3 steps that is all, now we start counting :)
1- In your global functions file put this lovely function
function exportMysqlToCsv($sql,$filename = 'export.csv')
{
$db=new clsDBWebecon();
$sql_exec = $db->query($sql.' LIMIT 0, 1');
$cols = @array_keys(@mysql_fetch_assoc($sql_exec));
$table = @implode(',',$cols)."\n";
$count_col = count($cols);
if(!$count_col) print get_text(no_work_don);
$sql_exec = $db->query($sql. ' LIMIT 0, 20000');
while ($db->next_record())
{
$col_array = NULL;
for($i = 0 ; $i < $count_col ; $i++)
{
$col_array[] = $db->f($cols[$i]);
}
$table .= @implode(',',$col_array)."\n";
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($table));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=$filename");
echo $table;
exit;
}
Tips : clsDBWebecon(); is my connection name , you know what you have to do :)
2- Make your select statement , and just select the column that you needs to be shown in the Excel sheet . do the inner join using the index to speed up the selection process.
Tips : use “select foo as Column_Name” taking in consideration that the mysql column name is the Excel column name.
3- when / where you like to make the download process please pass your sql statement to the function as following :
if(CCgetParam(export_to_csv) == 'yes')
{
exportMysqlToCsv($sql,'Export_Report('.rand(3232,232344).').csv');
}
That’s it :) :)
_________________
beshoo Love PHP and CC 4.2 |
