random product of the categories!!

[Tai]

http://www.oscommerce.com/forums/viewtopic.php...random+category I saw the way to random product which that code but it doesn't work for me.It seems the product image and product name do no match when random. So I looked for the new way and I found this code from randomproduct contribution.

 

$sql = "SELECT * FROM `products` ORDER BY RAND() LIMIT 1";

// execute SQL query and get result

$sql_result = tep_db_query($sql)

or die("Couldn't execute quasery.");



// format results by row

$random_products = mysql_fetch_array($sql_result);



$products_id = $random_products["products_id"];

$products_price = $random_products["products_price"];

$products_model = $random_products["products_model"];

$products_image = $random_products["products_image"];



$sql2 = "SELECT `pd.products_name`,`pd.products_short_description` from " . TABLE_PRODUCTS_DESCRIPTION . " pd, ". TABLE_PRODUCTS . " p, "  . TABLE_PRODUCTS_TO_CATEGORIES . " p2c " . " where p.products_status = '1'  and p.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '24 ' limit 1";

$sql2_result = tep_db_query($sql2)

or die("Couldn't execute query.");

$random_products2 = mysql_fetch_array($sql2_result);

$products_name = $random_products2["products_name"];

$products_short_description = $random_products2["products_short_description"];

 

but it is not for random product of the categories.

Anyone can code it?

Thank you

sorry for my poor english.

[mdfst13]

If I am understanding you correctly, the code you posted returns a single product from *any* category, and you want code to return from one specific category (i.e. if you sell DVDs and CDs, you want to select CD when in the CD section).

 

To do that, you just need to change the select statement so that it only includes products of the category you want.

 

Hmm...I tried to write an example and realized that this is harder than I thought. The problem is that the category is not part of the products table. This is not an impossible task, but it isn't as simple as I first thought. OK, start by modifying the select statement to pull an entry from the products_to_categories table rather than product. Also, specify the appropriate category. I am going to assume that the desired category is stored as $desired_category. If you already have a variable with that info, just substitute wherever you see $desired_category. If not, then you will have to set $desired_category before you get to this code.

 

// Select a product in the products_to_categories table where the

// category is the one desired.  Order all possible selections randomly

// and select only the first one.

// Note:  might be better to just get products_id, rather than *.

$sql = "SELECT * FROM `products_to_categories` WHERE categories_id=`" . $desired_category . "` ORDER BY RAND() LIMIT 1"; 

// execute SQL query and get result 

$sql_result = tep_db_query($sql) 

  or die("Couldn't execute query."); 

// store result in an array 

$random_p2c = mysql_fetch_array($sql_result); 

// set up sql query to return the product row like the original code did

$sql = "SELECT * FROM `products` WHERE products_id = `" . $random_p2c[ "products_id" ] . "`";

// now continue with original code, starting with the second line:

// execute SQL query and get result

 

Note: I have done 0 testing on this and have not even verified that $desired_category and $random_p2c are available names. Use at your own risk.

 

Hope this helps,

Matt

[Tai]

thank you for you code.I will try it tonight.

[Tai]

I 've got this message after I puted your code.

 

1052 - Column: 'products_id' in where clause is ambiguous

 

SELECT * FROM `products_description`,`products`,`products_to_categories ` WHERE products_id = '22' and language_id = '4' LIMIT 1

 

*********************************************************

$desired_category=24;

$sql = "SELECT * FROM `products_to_categories` WHERE categories_id=`" . $desired_category . "` ORDER BY RAND() LIMIT 1"; 

// execute SQL query and get result 

$sql_result = tep_db_query($sql) 

  or die("Couldn't execute query."); 

// store result in an array 

$random_p2c = mysql_fetch_array($sql_result); 

// set up sql query to return the product row like the original code did 

$sql = "SELECT * FROM `products` WHERE products_id = `" . $random_p2c[ "products_id" ] . "`"; 

// now continue with original code, starting with the second line: 

// execute SQL query and get result 

// execute SQL query and get result

$sql_result = tep_db_query($sql)

or die("Couldn't execute quasery.");



// format results by row

$random_products = mysql_fetch_array($sql_result);



$products_id = $random_products["products_id"];

$products_price = $random_products["products_price"];

$products_model = $random_products["products_model"];

$products_image = $random_products["products_image"];



$sql2 = "SELECT `products_name`,`products_short_description` FROM `products_description` WHERE products_id = '$products_id' and language_id = '" . $languages_id . "' LIMIT 1";

$sql2_result = tep_db_query($sql2)

or die("Couldn't execute query.");

$random_products2 = mysql_fetch_array($sql2_result);



$products_name = $random_products2["products_name"];

$products_short_description = $random_products2["products_short_description"];