Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Random products in main page


kAnAnA

Recommended Posts

I use this sql comand

$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);

 

to generate random products in main page.

Do you know any sql comand to generate random products in main page but ONLY random products about ONLY ONE CATEGORY and not include all the categories ??

 

thanks in advance

Link to comment
Share on other sites

I use this sql comand

$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);

 

to generate random products in main page.

Do you know any sql comand to generate random products in main page but ONLY random products about ONLY ONE CATEGORY and not include all the categories ??

 

thanks in advance

try this, replacing my 11 with the id you need

 

$theonecatyouwant = '11';

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS);

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

try this, replacing my 11 with the id you need

 

$theonecatyouwant = '11';

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

Thanks ... it?s working ... but with this code when i click in a main categorie it?s appear this error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/cs/public_html/includes/functions/database.php on line 99

 

What?s wrong ???

Link to comment
Share on other sites

Thanks ... it?s working ... but with this code when i click in a main categorie it?s appear this error

What?s wrong ???

it sound like the fetch array is not done or done incorrectly. Can I see the whole code up to the point where the loop starts?

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

it sound like the fetch array is not done or done incorrectly. Can I see the whole code up to the point where the loop starts?

well in database.php (original oscommerce file) the code is :

function tep_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link') {

reset($data);

if ($action == 'insert') {

$query = 'insert into ' . $table . ' (';

while (list($columns, ) = each($data)) {

$query .= $columns . ', ';

}

$query = substr($query, 0, -2) . ') values (';

reset($data);

while (list(, $value) = each($data)) {

switch ((string)$value) {

case 'now()':

$query .= 'now(), ';

break;

case 'null':

$query .= 'null, ';

break;

default:

$query .= '\'' . tep_db_input($value) . '\', ';

break;

}

}

$query = substr($query, 0, -2) . ')';

} elseif ($action == 'update') {

$query = 'update ' . $table . ' set ';

while (list($columns, $value) = each($data)) {

switch ((string)$value) {

case 'now()':

$query .= $columns . ' = now(), ';

break;

case 'null':

$query .= $columns .= ' = null, ';

break;

default:

$query .= $columns . ' = \'' . tep_db_input($value) . '\', ';

break;

}

}

$query = substr($query, 0, -2) . ' where ' . $parameters;

}

 

return tep_db_query($query, $link);

}

 

function tep_db_fetch_array($db_query) {

return mysql_fetch_array($db_query, MYSQL_ASSOC);

}

 

and in the new_products.php i have :

<?php

$theonecatyouwant = '133';

 

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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS); }

 

$row = 0;

$col = 0;

 

 

echo '

 

 

<table cellspacing=0 cellpadding=0>

<tr><td><img src=images/m23.gif width=490 height=43></td></tr>

<tr><td height=10></td></tr>

<tr><td class=bg1>

<table cellspacing=0 cellpadding=0>

<tr>

 

';

 

 

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

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

$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 '

 

<td width=165 valign=top>

<table cellspacing=0 cellpadding=0 width=130 align=center>

<tr height=39><td valign=top><img src=images/m98.gif width=6 height=9 hspace=5 vspace=3></td><td valign=top><a class=sc1 href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a></td></tr>

<tr><td height=10></td></tr>

<tr><td colspan=2 align=center><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, 'vspace=5') . '</a></td></tr>

<tr><td height=7></td></tr>

<tr><td colspan=2 class=sc2>Pre?o:<font color=#FF0000> '.$currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])).'</font></td></tr>

<tr><td height=7></td></tr>

<tr><td colspan=2><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . tep_image_button('small_view.gif') . '</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') . '</a></td></tr>

</table>

</td>

';

 

$col ++;

if ($col > 2) {

$col = 0;

$row ++;

echo '

 

 

</tr>

<tr><td colspan=5 height=10 bgcolor=#ffffff></td></tr>

<tr><td colspan=5><img src=images/m25.gif width=480 height=1></td></tr>

<tr><td colspan=5 height=10 bgcolor=#ffffff></td></tr>

<tr>

';

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

}

 

 

echo '

</tr>

</table>

</table>

';

 

 

?>

 

In first bold is the error in my main categorie page and in the second bold the sql code u give me .....

Link to comment
Share on other sites

change this

<?php
$theonecatyouwant = '133';

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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS); }

 

to this

<?php
$theonecatyouwant = '133';

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

you do not need the if ... it made you not have a query to fetch through.

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

change this

<?php
$theonecatyouwant = '133';

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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS); }

 

to this

<?php
$theonecatyouwant = '133';

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = '" . $theonecatyouwant . "' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

you do not need the if ... it made you not have a query to fetch through.

 

Thanks ..... only a litle litle litle problem .... now when a click in a main categorie it?s show?s me always that with id of $theonecatyouwant !!!

Link to comment
Share on other sites

Thanks ..... only a litle litle litle problem .... now when a click in a main categorie it?s show?s me always that with id of $theonecatyouwant !!!

 

uhhhhhhhmmmmmmmmm ... I thought you wanted this query to show one special cat only.

 

If not, you'll need to explain it once more. I really do not understand then.

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

uhhhhhhhmmmmmmmmm ... I thought you wanted this query to show one special cat only.

 

If not, you'll need to explain it once more. I really do not understand then.

 

Ok .... u help me a lot !! Big Thanks !!! but ....

i be sincere with u .... is not that i want .... what i want is select ONE by ONE the products in my main page ! and i don?t know how !!

yes .... i have used the contribuition "default products" but that change all my layout (new_products.php) ....

and i want preserve the layout ! Did u understand what i want ??

can u help with a simple solution ??

 

if u want u can take a look about my problem here in this oscommerce forum " http://www.oscommerce.com/forums/index.php?showtopic=208776 " .... but nobody help .... and u have cooperated ... so i have think that u can resolve my problem ...

can u ?

 

thanks in advance

Link to comment
Share on other sites

Ok .... u help me a lot !! Big Thanks !!! but ....

i be sincere with u .... is not that i want .... what i want is select ONE by ONE the products in my main page ! and i don?t know how !!

yes .... i have used the contribuition "default products" but that change all my layout (new_products.php) ....

and i want preserve the layout ! Did u understand what i want ??

can u help with a simple solution ??

 

if u want u can take a look about my problem here in this oscommerce forum " http://www.oscommerce.com/forums/index.php?showtopic=208776 " .... but nobody help .... and u have cooperated ... so i have think that u can resolve my problem ...

can u ?

 

thanks in advance

well looks like you need featured products then - didn't sound like that for sure in your initial briefing. As you are usng a template, customizing it is not a breeze - you will either have to do it trial and error or get (external) help.

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...