Help chaining a query?

Question

i am struggling to get the correct syntax for my query. The query is:

$query = "SELECT product_sku FROM #__virtuemart_products WHERE created_on <= NOW();";

I currently have:

$query
    ->select($db->quoteName(array('product_sku')))
    ->from($db->quoteName('#__virtuemart_products'))
    ->where($db->quoteName('created_on') . ' >=NOW();)
    ->order('ordering ASC');

But is is giving me a 500 server error.

Any ideas?

Best Regards Donna

Edit: This is so i am correctly using the Joomla API in my php. Edit 2: Full code is below:

<?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();";


//The query below needs fixing
/*$query->select($db->quoteName(array('product_sku')))
    ->from($db->quoteName('#__virtuemart_products'))
    ->where('created_on >= NOW()')
    ->order('ordering ASC');*/

$db->setQuery($query);

$result = $db->loadObjectList();
$result = $db->execute();
$my_count = $db->getNumRows($result); 
//echo($my_count);

//Check if any records are returned, if none then end
if ($my_count == 0){
echo "No records found";
exit;
}

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/website/public_html/temp_cron/new.csv';

if (file_exists($filename)) {
    array_map('unlink', glob($filename));
    //echo "The file $filename has been deleted";
}

$fp = fopen($filename, 'x');
if ($fp && $result) {
fputcsv($fp, array_values($head)); 
while ($row = $result->fetch_array(MYSQLI_NUM)) {
    fputcsv($fp, array_values($row));
}

}    


/********************************************
/
/Send new product sku's 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@hotmail.co.uk';

$mailer->addRecipient($recipient);

$body   = "New products have been added to the catalog.";
$mailer->setSubject('New Products Added');
$mailer->setBody($body);
// Optional file attached
$mailer->addAttachment($filename);

$send = $mailer->Send();

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

Answer 1

Try this code -

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

// Create a new query object.
$query = $db->getQuery(true); //-> you are missing this

//Select records that were created today
$query->select($db->quoteName(array('product_sku')))
    ->from($db->quoteName('#__virtuemart_products'))
    ->where($db->quoteName('created_on') . ' >= NOW()')
    ->order('ordering ASC');

$db->setQuery($query);

$result = $db->loadObjectList();
$result = $db->execute();
$my_count = $db->getNumRows($result); 

Also Please remove

$result = $db->execute();

Its not required here.

Some useful links

1. Selecting data using JDatabase
2. Inserting, Updating and Removing data using JDatabase

I hope it will work for you :)

Answer 2

The following is the proper order:

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

It is interesting, since for getting the results only, this code is enough:

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

Getting an 500 error because of this is ridiculous. I mean I got an nginx error page, so the exception was not caught by joomla...

Answer 3

Critically, you need to adjust your WHERE clause comparison so that you are collecting ALL of the products of the day. I have packed my snippet with inline comments, refinements, checkpoints, debugging features, and a single/concatenated response string to email yourself.

$body = "";
$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("product_sku")                   // string, not an array
                ->from("#__virtuemart_products")
                ->where("DATE(created_on) = CURDATE()")   // Y-m-d portion = Today's date
                ->order('ordering');                      // ASC is the default
    $db->setQuery($query);
    if (!$result = $db->loadAssocList()) {                // check and store for later
        $body = "<div>No Product Today (date: " . date("Y-m-d") . ")</div>";
        // I don't know if you want to delete the old .csv on this fork
    } else {
        $head = array_keys($result[0]);  // I'm not sure why this is dynamic (it's hardcoded as "product_sku")

        // Overwrite CSV
        $filename = '/home/website/public_html/temp_cron/new.csv';

        // Without *, no need to glob():  http://php.net/manual/en/function.unlink.php#109971
        if (file_exists($filename)) {
            if (unlink($filename)) {
                $body .= "<div>Deleted old .csv</div>";
            } else {
                $body .= "<div>Failed to deleted old .csv</div>";
            }
            $body .= "<div>Old .csv not found</div>";
        }

        $written = true;
        if (!$fp = fopen($filename, 'x')) {  // run and check
            $body .= "<div>Failed to generate handle for new .csv</div>";
            $written = false;
        } else {
            if (!fputcsv($fp, $head)) {  // run and check for failure or zero-length string
                $body .= "<div>Failed to write head row for new .csv (file write halted)</div>";
                $written = false;
            } else {
                foreach ($result as $row) {
                    if (fputcsv($fp, $row) === false) {  // run and check for success (allowing empty string)
                        $body .= "<div>Failed to write data row for new .csv (row: " . implode(" , ", $row) . ") (file write halted)</div>";
                        $written = false;
                        break;  // end the loop
                    }
                }
            }
            fclose($fp);
        }
        if ($written) {
            $body .= "<div>Successfully wrote " . sizeof($result) . " row(s) to the .csv</div>";
        }
    }
} catch (Exception $e) {
    $body = "Failure Occurred:<br>" . $query->dump() . "<br>" . $e->getMessage();
}
// ... proceed with emailing portion to deliver $body message to developer