quick question: cumulative limit

[lech]

Hi,

 

I want to limit the products in products_new.php to the last 20 added products. However simply applying the limit at the end of the query causes a problem because another limit is made immediately after due to the page splitting class.

 

The error is:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 0, 10' at line 1

 

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 products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added DESC, pd.products_name limit 0, 20 limit 0, 10

 

Thanks

 

Lech

[FalseDawn]

Change "Max Display New" in your Admin

[lech]

I believe that refers to the page split.

 

I want to limit the total number to the last 20 (as oppose to the products added this month). The only way I know to do this is to limit the table of all products ordered by date added with the LIMIT clause. Problem is: the splitPageResults class following the orginal query adds another limit clause to the end.

[lech]

Ok $5 to the first person to give me the solution. pm me your paypal email address.

 

Lech

[♥yesudo]

Try replacing:

 

$products_new_array = array();

$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";
?$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

 

 

With:

 

define('NUMBER_OF_NEW_PRODUCTS_TO_SHOW', '20');
$count = 0;
$where_clause = "(";

$np_query = tep_db_query("select p.products_id 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 limit " . NUMBER_OF_NEW_PRODUCTS_TO_SHOW);

while($np = tep_db_fetch_array($np_query))
{
?$where_clause .= "(p.products_id = " . $np['products_id'] . ")";
?$count++;
?if ($count < NUMBER_OF_NEW_PRODUCTS_TO_SHOW)
?{
? ?$where_clause .= " or ";
?}
}

$where_clause .= ")";

$products_new_array = array();
$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 ?" . $where_clause . " and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
?$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

 

 

Or another option rather than limiting by number of products is to limit by how new/old a product is:

 

http://www.oscommerce.com/community/contributions,1981

[lech]

yeah my man.

 

Lech