small script for xls-export from vm-data-warehouse, maybe someone need it ...

[slammy]

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:

Code Select Expand

<?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

[AH]

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/

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
excel 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


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.

[slammy]

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

[AH]

pls 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

Code Select Expand

<?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.

[GJC Web Design]

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 :

Code Select Expand

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.

Code Select Expand

$query = $db->getQuery(true);
$query
->select('name')
->from($db->quoteName('#__users'))
->where('id=45');
$db->setQuery($query);
$result = $db->loadResult() ;

[slammy]

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