Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

add date range to this working sql statement?


kev@num

Recommended Posts

does anyone know how to change this statment?

 

(it's taken from the allprods.php contribution)

 

i need to be able to limit this full product listing so that it only shows products added in the last, say 60 days.

 

 

$listing_sql = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' order by p.products_price, pd.products_name";
 include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);

 

if anyone can help with this it would be possible to change the amount of days in the code so anyone could adapt it for what they want...

 

thanks in advances :)

kev@num

Link to comment
Share on other sites

if anyone's interested, i've added this above the sql statement:

 

<?php
$today = getdate();
$temp_day = $today['mday'];
if($temp_day <= 28)
{
$this_day = $temp_day;
}
else
{
$this_day = '28';
}

$temp_month = $today['mon'];

if ($temp_month  > 2)
{
$this_month = $today['mon'] - 2; 
$this_year = $today['year'];
}
else
{ 
$this_month = $today['mon'] + 10; 
$this_year = $today['year'] - 1;
} 
?>

 

and changed the sql to this:

 

$listing_sql = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_date_added > ('" . $this_year . "-" . $this_month . "-" . $this_day."') and p.products_status = '1' order by p.products_price, pd.products_name";

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...