Hi community,
don´t know if this is the right place to post my small script here, if not, pls move it to the right board.
Sometimes I like to use my data (products, stock_level, prices) in a data_sheet like excel to aggregate amounts or to do other calculation with the data. Therefore I use this following small script to export my vm-product-data to an xls-based sheet which I can work on with openoffice or ms-office for example.
Maybe someone of you like to do the same, a button at the vm-datawarehouse-tablebased-list would be fine to call the script and do the export, but I am not experienced enough to do that in the core. What I do is calling the script manually and get my export into xls ...
create a new php file with following contents (define your own db-connection and db_prefix in the sql!) and save it:
<?php
// use connection
$link = mysql_connect('your mysql-server or socket', 'your db-user', 'your-db-password');
if (!$link) {
die('Connection not possible : ' . mysql_error());
}
// use database
$db_selected = mysql_select_db('your-db-name', $link);
if (!$db_selected) {
die ('Kann foo nicht benutzen : ' . mysql_error());
}
// do the SQL-Statement (of course u can upgrade the SQL with own field-definitions and more joined tables ...)
// don´t forget to change <hgm89> to your own table-prefix in the sql!)
$select="SELECT hgm89_virtuemart_products.virtuemart_product_id, product_in_stock, product_price, product_name from hgm89_virtuemart_products
LEFT JOIN (hgm89_virtuemart_product_prices, hgm89_virtuemart_products_de_de)
ON (hgm89_virtuemart_products.virtuemart_product_id=hgm89_virtuemart_product_prices.virtuemart_product_id AND hgm89_virtuemart_products.virtuemart_product_id=hgm89_virtuemart_products_de_de.virtuemart_product_id) ";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=warehouse_extraction_datasheet.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>
regards jens
My recommendation is not to put any code that store db passwords and connections in live, that is pretty insecure
Instead why not create local copies of the database for reporting and then work on them using heidisql http://www.heidisql.com/ (http://www.heidisql.com/)
Using "offline" data and direct local connections via either Heidisql or Excel ODBC will save you having to write specific code, reduces live server load and is Extremely flexible!
Updating the local dbase with a live db dump, takes seconds in heidisql.
Using heidisql, in many cases" will save you having to write specific code to get data as you can save the SQL result directly to a CSV file from heidisql
You can also build "snippets" of sql for re-use
you could connect to the database using the mysql odbc connector then create a report using excel and mysql
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html)
excel data connections
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx#BMunderstanding_data_connections (http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx#BMunderstanding_data_connections)
and
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx (http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx)
I hope this helps you and anyone out there that wants to get to the data and really get to grips with their store.
Give it a go.
Quote from: Hutson on June 23, 2014, 13:08:22 PM
My recommendation is not to put any code that store db passwords and connections in live, that is pretty insecure
pls explain to me the difference to configuratipn.php where all these informations are on every livesite, thank you.
regards jens
Quotepls explain to me the difference to configuratipn.php where all these informations are on every livesite
"every livesite" are you sure???
That is your second security hole :'(
If you have read around the security tightening of VM. You may have seen that most technical users "hide" the contents of the configuration.php from direct access
Details should not be in your public_html directory, you place them in a file below this directory and call this file using
configuration.php
<?php
require( dirname( __FILE__ ) . '/../joomla.conf' );
?>The original contents of configuration.php are stored in the joomla.conf file
You can also use this technique to store the db details outside of your code.
I hope this makes sense and will help you.
configuration.php is (or certainally should be) 400 attributes meaning it is only readable by the owner (php process)
By having the details in a file that is normally 644 "could" leave you open to a hacker reading them by various means
Your script can be useful bit it would be better to incl. the joomla framework -
something like :
define( 'DS', DIRECTORY_SEPARATOR );
$rootFolder = explode(DS,dirname(__FILE__));
//current level in directory structure
$currentfolderlevel = 1;
array_splice($rootFolder,-$currentfolderlevel);
$base_folder = implode(DS,$rootFolder);
if(!is_dir($base_folder.DS.'libraries'.DS.'joomla')) exit('Error: Could not loaded Joomla.');
define( '_JEXEC', 1 );
define('JPATH_BASE',implode(DS,$rootFolder));
// Include the Joomla framework
require_once ( JPATH_BASE .DS.'includes'.DS.'defines.php' );
require_once ( JPATH_BASE .DS.'includes'.DS.'framework.php' );
$application = JFactory::getApplication('site');
$application ->initialise();
$db = JFactory::getDbo();
then you can use standard (safe) Joomla calls
e.g.
$query = $db->getQuery(true);
$query
->select('name')
->from($db->quoteName('#__users'))
->where('id=45');
$db->setQuery($query);
$result = $db->loadResult() ;
Hi Hutson and GJC,
man I didn´t knew the facts. Thank you for explaining to me. I will do it the way you described to me hutson.
I thought I would be safe using chmod 500 on configuration.php and my script in a subfolder (of course I protected the Sub with htacess) ...
thank you for your posts and interest! I appreciate the help!
regards jens