Random New Products (HELP)

January 30, 2007

i have new_products.php in my index and i would like to integrate Random New Products but i don't know where to put it in the code.

<?php

/*

edited - 2005/07/01 13:50

-----------------------------------------

$Id: new_products.php,v 1.34 2003/06/09 22:49:58 hpdl Exp $

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

Released under the GNU General Public License

*/

?>

<?php

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

} else {

$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

}

$row = 0;

$col = 0;

echo '

';

while ($new_products = tep_db_fetch_array($new_products_query)) {

$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

$sql = 'SELECT `products_description` FROM `products_description` WHERE products_id ='.$new_products['products_id'].' && language_id='.(int)$languages_id;

$description_query = tep_db_query($sql);

$description = mysql_fetch_array($description_query, MYSQL_ASSOC);

$description['products_description'] = substr($description['products_description'], 0, 75);

$desc_len = strlen($description['products_description']);

$description['products_description'][$desc_len-1] = '.';

$description['products_description'][$desc_len-2] = '.';

$description['products_description'][$desc_len-3] = '.';

$product_query = tep_db_query("select products_description from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$new_products['products_id'] . "' and language_id = '" . (int)1 . "'");

$product = tep_db_fetch_array($product_query);

$new_products['products_description'] = $product['products_description'];

echo '

<tr>

<td><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $new_products['products_image'], $new_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, ' class=br') . '</a></td>

<tr height=70><td class=cy valign=top align=right><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a></td></tr>

<tr><td class=cy1 align=right>'.$currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])).'</td></tr>

</table>

</td>

</tr>

<tr><td>' . strip_tags($description['products_description']).'</td></tr>

<tr><td colspan=2 align=center><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . tep_image_button('small_view.gif', IMAGE_BUTTON_BUY_NOW) . '</a> <a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('button_in_cart.gif', IMAGE_BUTTON_BUY_NOW) . '</a></td></tr>

</table>

</td>

<!--

</table>

</td></tr>

</table>

</td>

--> ';

$col ++;

if ($col > 1) {

$col = 0;

$row ++;

echo '

</tr>

</table>

</table>

<tr>

';

} else echo '<td width=0></td>';

}

echo '

</tr></table>

</td></tr>

</table>

';

?>

January 30, 2007

just replace this code:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

withi this:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

January 30, 2007

just replace this code:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

withi this:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

Worked like a charm thanks.

January 30, 2007

Worked like a charm thanks.

yes it works, but i would like to limit the New Products to the last month for instance or instead to a specified category

Velveeta · January 30, 2007

yes it works, but i would like to limit the New Products to the last month for instance or instead to a specified category

Yeah the query up above will grab random products from the very 1st one you entered all the way up to the newest, try something like this:

Change (this is what you should currently have in place after changing the initial queries:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

To this:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and unix_timestamp(p.products_date_added) >= '" . mktime(0, 0, 0, date("m"), date("d"), date("Y")) . "' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and unix_timestamp(p.products_date_added) >= '" . mktime(0, 0, 0, date("m"), date("d"), date("Y")) . "' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

That should grab all new products from the current month only, and randomize the returned result set...

Richard.

January 30, 2007

Yeah the query up above will grab random products from the very 1st one you entered all the way up to the newest, try something like this:

Change (this is what you should currently have in place after changing the initial queries:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

To this:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and unix_timestamp(p.products_date_added) >= '" . mktime(0, 0, 0, date("m"), date("d"), date("Y")) . "' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and unix_timestamp(p.products_date_added) >= '" . mktime(0, 0, 0, date("m"), date("d"), date("Y")) . "' order by rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
}

That should grab all new products from the current month only, and randomize the returned result set...

Richard.

it works thanks, and if i want to limit the query to the last 20 New Products for instance

Velveeta · January 30, 2007

it works thanks, and if i want to limit the query to the last 20 New Products for instance

If you want to limit it to the last 20 new products, then you need to go back to the way the original query was, because it pulls strictly by the products_date_added, so will always put in the last X new products... But you wanted say, all new products for this month, which is why it's now modded to search for all products with a date_added greater than midnight of the 1st of the month, and you wanted them randomized, which is what the order by rand() does with the results... If you want to limit *those* results, then you'll need to set that under the maximum settings in your admin console to 20...

Richard.

January 30, 2007

If you want to limit it to the last 20 new products, then you need to go back to the way the original query was, because it pulls strictly by the products_date_added, so will always put in the last X new products... But you wanted say, all new products for this month, which is why it's now modded to search for all products with a date_added greater than midnight of the 1st of the month, and you wanted them randomized, which is what the order by rand() does with the results... If you want to limit *those* results, then you'll need to set that under the maximum settings in your admin console to 20...

Richard.

it's working, but perhaps i don't explain correctly. i would like to know if it's possible to make a rand () to the last 20 New Products for example.

Velveeta · January 30, 2007

it's working, but perhaps i don't explain correctly. i would like to know if it's possible to make a rand () to the last 20 New Products for example.

So are you saying you want to only focus on the most recent 20 products, but mix up the order they're displayed in everytime the page reloads? So it will always be the same 20 products per page load, but the display of them will be randomized?

Richard.

January 30, 2007

So are you saying you want to only focus on the most recent 20 products, but mix up the order they're displayed in everytime the page reloads? So it will always be the same 20 products per page load, but the display of them will be randomized?

Richard.

yes for example

Velveeta · January 30, 2007

yes for example

Ok, well, for example implies that that's *one* way you want to do this... But you can only do it one way, so, either you want it to pull strictly the last 20 new items and randomly sort their display, or you want it to pull 20 random products that are new as of the 1st of the month, and randomly rotate which of those 20 products are displayed... You can have one way, or the other, but you can't set a query to do one sometimes and the other the rest of the time...

Richard.

January 30, 2007

Ok, well, for example implies that that's *one* way you want to do this... But you can only do it one way, so, either you want it to pull strictly the last 20 new items and randomly sort their display, or you want it to pull 20 random products that are new as of the 1st of the month, and randomly rotate which of those 20 products are displayed... You can have one way, or the other, but you can't set a query to do one sometimes and the other the rest of the time...

Richard.

ok, i would like to pull strictly the last 20 new items and randomly sort their display

January 31, 2007

ok now is it possible to have random products display over a certain dollar ($) amount?

Velveeta · January 31, 2007

ok, i would like to pull strictly the last 20 new items and randomly sort their display

Sorry for the late reply, long day at work and a late dinner :) In order to randomly display the most recent X number of items, change my last posted queries to this (note, depending on your mysql version, this may not work... If it doesn't, I can show you a workaround, but you'll waste a query in the process):

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

As for displaying products over a certain $ amount, you'd want to do something like this (this will do the same as the previous queries, grab the most recent X products over a dollar amount, and randomly sort them for display):

$price = 50;
if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

Richard.

warrenerjm · January 31, 2007

Sorry for the late reply, long day at work and a late dinner :) In order to randomly display the most recent X number of items, change my last posted queries to this (note, depending on your mysql version, this may not work... If it doesn't, I can show you a workaround, but you'll waste a query in the process):

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

Richard.

Hi Richard

I am reading this with interest. I have PHP 4.4.2 Will this code above work for me? ie random last 20 new products? I don't really want to do it if I have to waste a query.

Thanks

Julie

Velveeta · January 31, 2007

Hi Richard

I am reading this with interest. I have PHP 4.4.2 Will this code above work for me? ie random last 20 new products? I don't really want to do it if I have to waste a query.

Thanks

Julie

Hiya Julie... I can't remember in which version mysql added support for nested select queries, so the only way to find out if this is going to work for you is to just plug it in and give it a try :) fortunately, the work is already done for you, all you have to do is comment your old queries out and drop these in, and if they don't work, just delete them and uncomment your old queries... The waste-a-query method I mentioned was basically to do a query first for the 20 most recent products_id's, loop through the results and stuff the id values into an array, then query again for all the info you need (products_name, price, etc) where products_id in (implode that array) order by rand(), which would accomplish the same thing, but the first query would be a throwaway since it's just providing you id's to query on in the 2nd...

Richard.

January 31, 2007

As for displaying products over a certain $ amount, you'd want to do something like this (this will do the same as the previous queries, grab the most recent X products over a dollar amount, and randomly sort them for display):

$price = 50;
if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

Richard.

I'm getting this error

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/premierw/public_html/shop/includes/modules/new_products.php on line 42

Is is supposed to have an } at the end of it? And also would I just change the $price = 50; to the price I want it to display and is it only that price or atleast that price and higher

Velveeta · January 31, 2007

I'm getting this error
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/premierw/public_html/shop/includes/modules/new_products.php on line 42
Is is supposed to have an } at the end of it? And also would I just change the $price = 50; to the price I want it to display and is it only that price or atleast that price and higher

Sorry about that, in the 2nd query there's a missing quotation mark, change this at the end of it:

order by rand());

to this:

order by rand()");

Richard.

January 31, 2007

Ok now that works but when I go to the subcategories I get this

1248 - Every derived table must have its own alias

select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '21' and p.products_status = '1' and p.products_price > '0.5' order by p.products_date_added desc limit 10) order by rand()

[TEP STOP]

Velveeta · January 31, 2007

select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '21' and p.products_status = '1' and p.products_price > '0.5' order by p.products_date_added desc limit 10) as newtbl order by rand()

Sorry about that too :D Add the bolded text in where you see it above...

Richard.

January 31, 2007

select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '21' and p.products_status = '1' and p.products_price > '0.5' order by p.products_date_added desc limit 10) as newtbl order by rand()

Sorry about that too :D Add the bolded text in where you see it above...

Richard.

That's already there

Or do I have to add that in another file?

Velveeta · February 1, 2007

select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '21' and p.products_status = '1' and p.products_price > '0.5' order by p.products_date_added desc limit 10) order by rand()

The above is the query you posted a couple posts back, where you were getting the mysql error... in that query, there's no "as newtbl" at the end of that, before the order by rand() section... The above query should be the 2nd of those 2 queries, as it has a category filter on it... Go ahead and double-check both of those queries to make sure they both have that "as newtbl" in them, because they'll both need it in order for them to work right...

Richard.

February 1, 2007

The above is the query you posted a couple posts back, where you were getting the mysql error... in that query, there's no "as newtbl" at the end of that, before the order by rand() section... The above query should be the 2nd of those 2 queries, as it has a category filter on it... Go ahead and double-check both of those queries to make sure they both have that "as newtbl" in them, because they'll both need it in order for them to work right...

Richard.

You lost me when you said both queries? can you elaborate a little more

Sorry for sounding like a newbie.

Velveeta · February 1, 2007

You lost me when you said both queries? can you elaborate a little more
Sorry for sounding like a newbie.

This is the last full set of queries I posted a few messages back, spaced out so you can see what I'm talking about:

$price = 50;

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select * from (select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");

} else {

$new_products_query = tep_db_query("select * from (select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

[/code

If you look at it, you'll see 2 queries there, one inside the first section of the "if" block, and the next after the "else"... The one under the else is the one that was missing the quotation mark at the end of "order by rand()", and it's also missing the "as newbtl" right before the "order by rand()"... That's the one you'll need to fix...

Richard.

February 1, 2007

Ok thanks.

Random New Products (HELP)

Recommended Posts

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Archived