[SQL] Can you automate an SQL query in the database manager?

[Nickovitshj]

select ps_order_history.id_order, ps_order_history.date_add, ps_order_state_lang.name from ps_order_history INNER JOIN ps_order_state_lang ON ps_order_history.id_order_state=ps_order_state_lang.id_order_state where id_lang = 1 order by id_order, date_add;

I'd like to automatically export the .csv resulting from this SQL query.
Can this only be done through a php script?

If so, can anyone point me in the right direction regarding that?

Kind regards,
Nick

[Crezzur.com]

Something like this will get you going

<?php
    // PHP file needs to be place: yourstore/adminfolder/-YOUR_PHP_FILE-
    // Check if _PS_ADMIN_DIR_ is defined
    if (!defined('_PS_ADMIN_DIR_')) {
        // if _PS_ADMIN_DIR_ is not defined, define.
        define('_PS_ADMIN_DIR_', getcwd());
    }
    // Setup connection with config.inc.php (required for database connection, ...)
    include(_PS_ADMIN_DIR_.'/../config/config.inc.php');

    $data = Db::getInstance()->executeS('SELECT his.id_order, his.date_add, sl.name FROM '. _DB_PREFIX_ .'order_history AS his
        INNER JOIN '. _DB_PREFIX_ .'order_state_lang AS sl ON his.id_order_state = sl.id_order_state WHERE id_lang = 1 ORDER BY id_order, date_add');
 
    if ($data) {
        $delimiter = ","; 
        $filename = "crezzur_" . date('Y-m-d') . ".csv"; 

        // Create a file pointer
        $f = fopen('php://memory', 'w'); 

        // Set column headers
        $fields = array('id_order', 'date_add', 'name'); 
        fputcsv($f, $fields, $delimiter); 

        // Output each row of the data, format line as csv and write to file pointer
        foreach ($data as $row) {
            $lineData = array($row['id_order'], $row['date_add'], $row['name']); 
            fputcsv($f, $lineData, $delimiter); 
        }

        // Move back to beginning of file and set header
        fseek($f, 0);
        header('Content-Type: text/csv'); 
        header('Content-Disposition: attachment; filename="' . $filename . '";'); 
        fpassthru($f); 
    }

 

[Nickovitshj]

Thanks Crezzur, i'll be taking a look at this.

[Nickovitshj]

Works perfectly, 
his and sl are known PrestaShop shortcuts?

Any idea by any chance how we could get this file sent as an attachment over mail?

Regards,

Nick

[Nickovitshj]

2 hours ago, Nickovitshj said:

Works perfectly, 
his and sl are known PrestaShop shortcuts?

Any idea by any chance how we could get this file sent as an attachment over mail?

Regards,

Nick

Nevermind, I just noticed you set them as such.

The question about the email remains though.

[Crezzur.com]

2 hours ago, Nickovitshj said:

Nevermind, I just noticed you set them as such.

The question about the email remains though.

By adding a mail function to the same code, something like this (more info here)

        Mail::Send(
            (int)(Configuration::get('PS_LANG_DEFAULT')), // defaut language id
            'contact', // email template file to be use
            ' Module Installation', // email subject
            array(
                '{email}' => Configuration::get('PS_SHOP_EMAIL'), // sender email address
                '{message}' => 'Hello world' // email content
            ),
            Configuration::get('PS_SHOP_EMAIL'), // receiver email address
            NULL, //receiver name
            NULL, //from email address
            NULL,  //from name
            NULL, //file attachment
            NULL, //mode smtp
            _PS_MODULE_DIR_ . 'yourmodulename/mails' //custom template path
        );

 

Quote

his and sl are known PrestaShop shortcuts?

No, but like you see yourself already i like to set the table names to something short to prevent big querys.

 

Edited August 17, 2021 by Crezzur (see edit history)