2

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;
?>
  • Hi Donna, I'm not sure if MySQL questions are welcome here or not. Either way, sharing the error message will probably get you a faster answer, hopefully before the question is closed. – Neil Robertson Mar 11 '16 at 10:27
  • Hi @Neil Robertson, while this is an SQL statement it is using the Joomla API, thus i am asking how i can correctly format the statement to be used in my joomla code. – Dtorr1981 Mar 11 '16 at 10:47
  • Yes, this should help keep the question open and thanks for the clarification on the error. – Neil Robertson Mar 11 '16 at 10:50
  • you have ';' here -> ->where($db->quoteName('created_on') . ' >=NOW();) – Joomler Mar 11 '16 at 10:57
  • I have removed it and the apostrophe causes the rest of the code to be a comment. I have removed the apostrophe aswell and still get the 500 error. – Dtorr1981 Mar 11 '16 at 11:04
4

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 :)

| improve this answer | |
  • Thank you, this gives me 2 errors firstly : Notice: Undefined variable: query in...on line 26 (the line where $query is) and Fatal error: Call to a member function select() on a non-object in...on the same line. – Dtorr1981 Mar 11 '16 at 11:53
  • @Dtorr1981 can you please share your whole code? its hard to guess whats you are trying. – Joomler Mar 11 '16 at 11:58
  • I have updated my original post – Dtorr1981 Mar 11 '16 at 12:15
  • I updated my answer, please check :) – Joomler Mar 11 '16 at 12:16
  • thank you the query works now, however if i remove $result = $db->execute(); i get an error: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, array given in /home/#####/public_html/libraries/joomla/database/driver/mysqli.php on line 343. It runs fine if it is included. – Dtorr1981 Mar 11 '16 at 12:25
1

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

| improve this answer | |
0

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
| improve this answer | |
  • @Dtorr1981 look closely at: w3resource.com/mysql/date-and-time-functions/… Can your cronjob generate a create_on stamp that is greater (in the future) than the current datetime? Does your accepted solution actually work? It either doesn't or I don't understand your data. – mickmackusa Dec 8 '18 at 11:58

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.