Page 1 of 1

filter a record from joined tables not working

Posted: Thu Sep 20, 2012 4:19 am
by jonnyfortis
Hello. 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.

Code: Select all

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

Code: Select all

$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