User reports that can export to excel

7 years 9 months ago #171598 by IAAF
I manage a site for a customer and this customer would like to have user reports sent to him in excel since they do not go into the backend. They would like reports by CB subscription plan, include our custom CB fields, expiration dates of plans, status of user, etc. I have searched and searched and have not yet found anything that was just report based vs performing DB functions and that could just be clicked to generated an excel spreadsheet. Does anyone have the basic SQL to read CB users & subscription info that I could see - then I could tweak for my own purposes?
7 years 5 months ago #185770 by sakarora
hey i did so much of research on this particular thing but all i found the best method for me.... i use Art Table Lite Edition to fetch user reports... and for exporting it i use Flexi Custom Code... which runs php scripts in joomla... so i wrote script in php... and made a tab linked with Flexi Custom Code page... this way i did it... if u need full explanation you can ask me.... i can provide you script as well if you want it... :)
7 years 5 months ago - 7 years 5 months ago #185901 by sakarora
here is the code:
<?php
$con=dbconnect();
function dbconnect() //Function to connect to the DB and to select the database from it
{
$db_server="localhost"; //hostname
$db_db="joomla"; // database name
$db_user="root"; // username
$db_password="your password"; //password
$con = mysql_connect($db_server,$db_user,$db_password);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($db_db, $con);
return $con;
}
function exportMysqlToCsv($table,$filename = 'Roster.csv') //your file name
{
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = ""; //your sql query
$result = mysql_query($sql_query);
$fields_cnt = mysql_num_fields($result);
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++)
{
$l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
$schema_insert .= $l;
$schema_insert .= $csv_separator;
}
$out = trim(substr($schema_insert, 0, -1));
$out .= $csv_terminated;
while ($row = mysql_fetch_array($result))
{
$schema_insert = '';
for ($j = 0; $j < $fields_cnt; $j++)
{
if ($row[$j] == '0' || $row[$j] != '')
{
if ($csv_enclosed == '')
{
$schema_insert .= $row[$j];
} else
{
$schema_insert .= $csv_enclosed .
str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
}
} else
{
$schema_insert .= '';
}
if ($j < $fields_cnt - 1)
{
$schema_insert .= $csv_separator;
}
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename");
echo $out;
exit;
}
$table= "tablename"; // this is the tablename that you want to export to csv from mysql.
exportMysqlToCsv($table);
?>
Moderators: beatnantkrileon
Time to create page: 0.393 seconds
Facebook Twitter Google LinkedIn