Restrict date for new products

[dougedmunds]

My server is using MYSQL 4.0.16, which does not have the enhanced

date-field functions of 4.1+

 

I want to modify the query that selects new products to limit it to only

those greater than a certain date.

 

The products_new.php file uses this query, which orders the result in DESC order:

 

$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

 

 

What I need is a "where" clause that will limit the p.products_date_added field to

only those >= Sept 1, 2004 and < Oct 1, 2004.

 

Also, I would prefer to allow modification of this range of values by admin (probably best on the Configuration > Max values page).

That page store all values in the Configuration table in a varchar field.

So, I also need is code to convert two "date" fields (the low-date and high-date values) from a varchar to a date, so they can then be used as variables in the where clause of the modified MYSQL query (i.e., where p.products_date_added >= $low_date and p.products_date_added < $high_date).

 

Thanks in advance!

[dougedmunds]

Well, since no one suggested a solution, I did it myself!

 

1. change the query to read:

 

$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_date_added >= " . NEW_PRODUCTS_DATE_START . " and p.products_date_added <= " . NEW_PRODUCTS_DATE_END . " order by p.products_date_added DESC, pd.products_name";

 

2. to the configuration table, add two new constants

 

NEW_PRODUCTS_DATE_START

and

NEW_PRODUCTS_DATE_END

 

(I put them in Group 8, products listings, but you might put them in

a different group).

 

3. Because the constants are stored as VARCHARS, you are storing strings not dated. So that Mysql can compare them to datetime fields, store these 'date' values this way

 

4DigitYear2DigitMonth2DigitDay, with no spaces, for example:

 

Jan 31 2004 = 20040131

Dec 4, 2005 = 20051204

 

Be sure to give an example in the configuration_description field for each

new constant you add!

 

4. MYSQL can compare (<, >, etc) a datetime field to a varchar if it is in that form, without requiring that the varchar be converted into a datetime field first. So the query listed above will compare the constants to the date_added values and only show those records which are within the range as "new".

 

Note what looks like three single quotes in a row in the query is really either a double then a single or a single then a double.

[kahml]

Thank you!

 

I was looking for someone to have encountered and explained how to do this. Your key of VARCHAR was enormously helpful!

 

Since I only want items that were added in the current month - and I didn't want to bother with a table entry - I simply defined a new constant:

 

  // limit new products to those added during the current month
define('LMK_CURRENT_DATE', date("Ym",time()).'01');

 

which is set for the first day of the current month. This way I don't ever have to worry about updating any tables (the code takes care of itself).

 

My comparison looks like:

 

and p.products_date_added >= " . LMK_CURRENT_DATE

 

Which works quite nicely.

 

Larry

[bjhampe]

Where do I find the configuration table?

[dougedmunds]

Where do I find the configuration table?

<{POST_SNAPBACK}>

The configuration table is in whichever mysql database you created

when you installed oscommerce. You will need to use

phpmyadmin or something similar to get to it. You can't make this

change thru OSCommerce admin.

 

If your tables have prefixes, then you the table may be called

something like osc_configuration, instead of plain ol' configuration.

 

HEY NOW! make a backup of the database before you make

any changes through phpmyadmin.

 

If tables and backups and phpmyadmin are not your thing, get someone

who understands this stuff to do it for you. You only need to add 2 records

to 1 table, the rest is changes to php code.

[Guest]

JanZ came up with a similar solution to this problem, but it's takes a slightly different approach: products are considered "new" for x number of days, not just for this calendar month.

 

IOW, you specify that products are to be new for 30 days, or 60 days, etc. Any product added w/in the last 30 days is then displayed.

 

The thread is here: http://www.oscommerce.com/forums/index.php?sho...ndpost&p=499243

 

-jared