filtering multiple recordset. Desperate

[jonnyfortis]

Hello

I am really desperate for help can someone look at my code and see where i am going wrong.

I have three tables in a PHP Mysql database all joined. I will show the joined feilds

 

Catagory Table

catID

catname

 

Product Table

ID

catID

SizeProdID

 

and a Size Table

SizeID

SizeProdID

catname

 

 

I have a product page showing all products (by images) from a product table.

 

mysql_select_db($database_beau, $beau);
$query_Recordset1 = "SELECT * FROM beauProd ORDER BY name ASC";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $beau) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

<a href="product-description.php?ID=<?php echo $row_Recordset1['ID']; ?>"><img src="../images/AW/thumbs/<?php echo $row_Recordset1['imageSmall']; ?>"/></a>

 

on the detail page i need to get information from ALL the tables

 

the first part is I need to have a select list that shows all the products from that catagory and when another product is select form the list and submitted is shows the new product

 

so here is this script

$colname_Recordset1 = "-1";
if (isset($_GET['ID'])) {
  $colname_Recordset1 = $_GET['ID'];
}
mysql_select_db($database_beau, $beau);
$query_Recordset1 = sprintf("SELECT * FROM beauProd WHERE ID = %s", GetSQLValueString($colname_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $beau) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = "-1";
if (isset($_GET['ID'])) {
  $totalRows_Recordset1 = $_GET['ID'];
}

<select name="name" class="text" id="selectName">
                  <option value="Select Design">Select Design</option>
                  <?php
do {
?>
                  <option value="<?php echo $row_Recordset1['ID']; ?>"><?php echo $row_Recordset1['name']; ?></option>
                  <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
            $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>
                </select>

i then need to show in another select list the sizes from the product selected in the previous select list

$colname_Recordset2 = "-1";
if (isset($_GET['SizeProdID'])) {
  $colname_Recordset2 = $_GET['SizeProdID'];
}
mysql_select_db($database_beau, $beau);
$query_Recordset2 = sprintf("SELECT * FROM beauProd, beauSizes WHERE beauSizes.SizeProdID = beauProd.SizeProdID AND beauProd.SizeProdID = %s", GetSQLValueString($colname_Recordset2, "int"));
$Recordset2 = mysql_query($query_Recordset2, $beau) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

<select name="os0" class="text" id="Cos0">
    <option value="select Category">Select Size</option>
    <?php
do { 
?>
    <option value="<?php echo $row_Recordset2['SizeProdID']; ?>"><?php echo $row_Recordset2['from'] . " - " . $row_Recordset2['to'] ; ?></option>
    <?php
} while ($row_Recordset2 = mysql_fetch_assoc($Recordset2));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset2, 0);
            $row_Recordset2 = mysql_fetch_assoc($Recordset2);
  }
?>
  </select>

 

this is basically what i need to achieve but currently the select list looking for the produts is just showing the product the page is for and the size list is showing nothing

 

any help would be greatly appreciated

[Jessica]

Your PHP isn't in <?php ?> tags...

[jonnyfortis]

ok this is good i need to learn. can you just answer. can this be fixed or am i going about this the wrong way?

[Jessica]

TBH I don't completely understand your question. If you want a list of all products to display on a page, you need to select all products.

[jonnyfortis]

OK i will try and explain more clearly, want just the products in that category to be displayed. From the main product page i am linking to the detail page via the product ID but when i am on that product page i need to call all other products in a select list from the same category.

 

When a new product is selected from the select list i need to send it to that said product.

 

 

[jonnyfortis]

Also on each product page i need to call a product size table that is joined to the product table via a prodSizeID (the feild is also in the size table to create a join)