filter a record from joined tables not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

filter a record from joined tables not working

Post 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
Post Reply