0

I am trying to email the results of a query as an attached .csv file to my admin email.

The code correctly saves the .csv file, but the email is never actually sent.

If I put the sendmail code into its own php document, it does send the mail. I'm stumped.

<?php

//display errors
error_reporting(E_ALL);
ini_set("display_errors", 1);

//init Joomla Framework 
define('_JEXEC', 1); 
define('JPATH_BASE', dirname(__FILE__).'/');
define('JPATH_COMPONENT',dirname(__FILE__).'/');
define('DS', DIRECTORY_SEPARATOR); 

//echo JPATH_BASE;

require_once (JPATH_BASE .DS. 'includes' .DS. 'defines.php'); 
require_once (JPATH_BASE .DS. 'includes' .DS. 'framework.php');

// Start database stuff
$db = JFactory::getDbo();

//Select records that were created today
$query = "SELECT product_sku FROM #__virtuemart_products WHERE created_on <= NOW();";

$db->setQuery($query);
//$result = $db->loadObjectList();
$result = $db->execute();    

if (!$result) die('Couldn\'t fetch records');

$headers = $result -> fetch_fields();
foreach ($headers as $header) {
    $head[] = $header->name;
}    

// Check if the file already exists, if so delete it

$filename = '/home/#####/public_html/new_products/new.csv';

if (file_exists($filename)) {
    array_map('unlink', glob("/home/#####/public_html/new_products/new.csv"));
    //echo "The file $filename has been deleted";
}

$fp = fopen('/home/#####/public_html/new_products/new.csv', 'x');
if ($fp && $result) {
    fputcsv($fp, array_values($head)); 
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}    

//Send file to admin    
$mailer = JFactory::getMailer();

$config = JFactory::getConfig();
$sender = array( 
    $config->get( 'mailfrom' ),
    $config->get( 'fromname' ) 
);

$mailer->setSender($sender);
echo "it works";
$user = JFactory::getUser();
$recipient = 'name@email.com';

$mailer->addRecipient($recipient);

$body   = "Your body string\nin double quotes if you want to parse the \newlines etc";
$mailer->setSubject('New Products Added');
$mailer->setBody($body);
// Optional file attached
$mailer->addAttachment('/home/ladyloving/public_html/new_products/new.csv');

$send = $mailer->Send();

if ( $send !== true ) {
    echo 'Error sending email: ' . $send->__toString();
} else {
    echo 'Mail sent';
}

?>

I expect to see it works because of echo "it works";, but it doesn't display.

  • You're still not using the Joomla API fully like I showed you, for your database query. The way you're currently doing it is native SQL, and you have not escaped any strings. – Lodder Mar 10 '16 at 19:36
  • As for the actual issue, have you tried using var_dump(); exit; along the way to see if variables are actually being populated? – Lodder Mar 10 '16 at 19:37
  • Hi Lodder, i couldnt get the query to work using the API like you said, so figured i would get the rest working and go back to it. The code works separately, i.e. file is saved on the server when the export code is run, and if i run the send mail code on its own it locates the file and emails it to me. But together in the same file i just get a white page? No errors or anything. this is why im confused. – Dtorr1981 Mar 10 '16 at 19:41
  • White page means there is an error. In your Joomla Global Configuration, set error reporting to development – Lodder Mar 10 '16 at 19:46
  • This is set to development, but still nothing shows up. I have uncommented echo "The file $filename has been deleted"; and this shows in the browser, but nothing else? – Dtorr1981 Mar 10 '16 at 19:53
0

Looks like you're missing a die statement:

$fp = fopen('/home/#####/public_html/new_products/new.csv', 'x');
if ($fp && $result) {
fputcsv($fp, array_values($head)); 
while ($row = $result->fetch_array(MYSQLI_NUM)) {
    fputcsv($fp, array_values($row));
}
die; <-- it's probably stopping here
}    
  • Thank you, the die statement was already present in that location? I have just looked up die in the php manual and it is clear that it should not be in that location. I have moved it to the end of the code, after the mail has been sent, is that correct? – Dtorr1981 Mar 11 '16 at 8:52
  • Typically you'd only use a die either to dump a variable and halt execution, or to bail out when something went wrong (or use exit, same thing). Normally you wouldn't use these unless it was required, there's much better ways to handle errors. – user101289 Mar 13 '16 at 5:02
0

die() is what is prematurely killing your script, yes, but there are other critical issues to fix -- not only so that your design works as intended, but so that copy-paste developers are using the correct techniques. First my recommended script, then some explanations and logical advice.

// Start database stuff
$db = JFactory::getDbo();
$query = $db->getQuery(true)
            ->select("product_sku")
            ->from("#__virtuemart_products")
            ->where("created_on >= CURDATE()"); // SKUs created since today (Y-m-d 00:00:00)
$db->setQuery($query);

$filename = '/home/#####/public_html/new_products/new.csv';
if (!$result = $db->loadRowList()) {  // generate indexed array of indexed arrays or null
    $subject = "No Records Today";
} else {
    $subject = "New Products Added Today";
    if (!$fp = fopen($filename, 'w+')) {  // no need to delete file beforehand
        $attachment = false;
    } else {
        $attachment = true;
        fputcsv($fp, ['Product SKUs']);  // write the known header value
        foreach ($result as $row) {
            fputcsv($fp, $row);  // single element array in this case
        }
        fclose($fp);  // never forget this step
    }
}

//Send file to admin
$mailer = JFactory::getMailer();
$config = JFactory::getConfig();
$sender = [
    $config->get('mailfrom'),
    $config->get('fromname') 
];
$mailer->setSender($sender);

$user = JFactory::getUser();
$recipient = 'name@email.com';

$mailer->addRecipient($recipient);

$body   = "Your body string\nin double quotes if you want to parse the \newlines etc";
$mailer->setSubject($subject);
$mailer->setBody($body);

if ($attachment) {
    // Optional file attached
    $mailer->addAttachment($filename);
}

$send = $mailer->Send();

if ( $send !== true ) {
    echo 'Error sending email: ' . $send->__toString();
} else {
    echo 'Mail sent';
}
  • Your query is returning a result set of data comprised of "All SKUs which have a create_on datetime stamp which is before the current datetime stamp". Well, unless your project affords the ability to INSERT a row and assign a create_on datetime that is "in the future", your SELECT query will be returning ALL SKUs in your database table because that is what created_on <= NOW() (http://www.mysqltutorial.org/mysql-now/) does.

    Instead, isolate the Y-m-d portion of each row's created_on value and match that against the current date to collect only the SKUs that were created today. More efficiently than removing all of time substrings from your timestamps, you can just check if the timestamp is equal to or greater than the start of today. This is what my snippet now does.
  • Because you are only seeking the product_SKU column values, I don't think I would bother writing a dynamic $headers process; just hardcode Product SKU into the first line of your csv file. Granted, if you are actually generating a multi-column csv, your process is fine -- just change my loadRowList() to loadAssocList() and use your looping step.
  • There is no need to preemptively delete the filename from the chosen directory if you call fopen() with w+ as the second parameter -- this will seamlessly overwrite a pre-existing file IF there is one, otherwise it will just create the file. Effectively, you can remove the whole hunk of deletion code; and for the record, array_map('unlink', glob("/home/#####/public_html/new_products/new.csv")); doesn't make sense because there can only either be zero or one of the filename, so you could have more easily called unlink("/home/#####/public_html/new_products/new.csv"). ...but, again, just scrap this whole section. Remember to ALWAYS fclose() after calling fopen().
  • die; is something that developers use to halt the execution of a script at a certain point to test functionality up to that point so that they can assess the processes without executing the script that follows. While testing the creation of the csv file, it would have made sense to not fill the recipient's inbox with test files. Once you have determined that that leading portion of code is working as desired, the die line should be removed so that the rest of the script can run.
  • fputcsv() is "key-ignorant" so you don't need to bother calling array_values() (which assigns new indexes to an array) -- just feed the row data to the function calls.
  • It is possible that your clients will have a slow business day and add no new product. For this reason, I feel it is more sensible to make this distinction in the email Subject and avoid sending an empty csv file. My script using $subject and $attachment to enable this kind of execution. So now, setSubject() receives a variable and addAttachment() is conditionally performed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.