Random Product Script - missing product_id

[unisupuk]

Hi all

I have recently been developing a small php script to pull and display random products from the Zen database and display them on the front page of our site www.premierbluetooth.com.

------------------------CODE SAMPLE------------------------------------

<DIV ID="random_product" STYLE="width: 120px; border: 1px dashed #006699">
<CENTER>
<B style="color: #006699"><?php

$dbusername="*************";

$dbpassword="*************";

$dbname="**************";

$chandle = mysql_connect("localhost", $dbusername, $dbpassword) or die("Connection Failure to Database. Try again Mr Duffin!");

mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found." . $dbuser);

$randprod=rand(1, 51);

$sql = "Select products_name FROM zen_products_description WHERE products_id = '" .$randprod. "'" ;

$demoResult = mysql_query( $sql );

$array = mysql_fetch_array($demoResult);

echo $array[0];

?></b><br><br>
<img width="100px" src="http://www.premierbluetooth.com/shop/images/
<?php

$sql = "Select products_image FROM zen_products WHERE products_id = '" .$randprod. "'" ;

$demoResult = mysql_query( $sql );

$array = mysql_fetch_array($demoResult);

echo $array[0];
?>
">
<br><br>
<form name="cart_quantity" action="http://www.premierbluetooth.com/shop/?action=add_product" method="post" enctype="multipart/form-data">
<input type="hidden" name="cart_quantity" value="1" maxlength="6" size="4" />
<input type="hidden" name="products_id" value="<?php echo $randprod ?>" />
<a id="buythis" class="buythis" href="javascript:cart_quantity.submit()" >Buy This Item</a>
</form>
<?php
mysql_close($chandle);
?>
</CENTER>
</DIV>
------------------------CODE SAMPLE------------------------------------

The code works (i thought) by selecting a random number within our product range and then displaying its name, image and a direct "add to order" link.

How ever, it sometimes doesn't display anything and put an image placeholder on the page, and after checking our database, I have come to the conclusion that as products are added and removed their ID number doesn't automatically update to consecutive numbers, meaning my random poduct code fails on certain ID's.

Is there a method of "cleaning" the database of redundant ID's and then re-ordering them with the new range, or does this require a complete rebuild of the database?

I would like to release this as an external module for zencart when it is completed, as we feel it could be useful to sites that run affiliate schemes, as well as placing new content on pages other than those within the Zencart system (premierbluetooth runs an HTM based site above the zencart site for selling mobiles).

Any help would be much appreciated!

Ben Duffin
unisupUK

[kobra]

If there are no attributes applied, I think without looking that the only cross linkage is between the categoty table and the product tables (I think there are more than one for product) and you would have to manually reorder the table and then recreate the linkage.

What happens as one adds more items & deletes items??.