Items in multiple categories

[Travis250923]

I'm trying to allow for items to be in multiple categories but I can only get one product to show on the products page. I have set up a relational table and used dataassist to combine the items table and the itemcategory. I have sorted the recordsets using the following code but it results in only the first itemid in the catlink recordset which is the relational table.

When I echo the itemid in the cat link I get the appropriate ids but when I echo itemid from the items recordset I only get one id repeated several times which means I only get 1 item on the page even though there should several. Any help would be great.

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname_rsCatLink = "-1";
if (isset($_GET['ItemCatID'])) {
$colname_rsCatLink = (get_magic_quotes_gpc()) ? $_GET['ItemCatID'] : addslashes($_GET['ItemCatID']);
}
mysql_select_db($database_waConn, $waConn);
$query_rsCatLink = sprintf("SELECT * FROM itemcategorylink WHERE ItemCatID = %s", GetSQLValueString($colname_rsCatLink, "int"));
$rsCatLink = mysql_query($query_rsCatLink, $waConn) or die(mysql_error());
$row_rsCatLink = mysql_fetch_assoc($rsCatLink);
$totalRows_rsCatLink = mysql_num_rows($rsCatLink);

$_GET['IDFORITEMS'] = $row_rsCatLink['ItemID'];

$colname_rsItems = "-1";
if (isset($_GET['IDFORITEMS'])) {
$colname_rsItems = (get_magic_quotes_gpc()) ? $_GET['IDFORITEMS'] : addslashes($_GET['IDFORITEMS']);
}
mysql_select_db($database_waConn, $waConn);
$query_rsItems = sprintf("SELECT * FROM items WHERE ItemID = %s ORDER BY ItemName ASC", GetSQLValueString($colname_rsItems, "int"));
$rsItems = mysql_query($query_rsItems, $waConn) or die(mysql_error());
$row_rsItems = mysql_fetch_assoc($rsItems);
$totalRows_rsItems = mysql_num_rows($rsItems);

$_GET['TEMPIDFORRS'] = $row_rsItems['ItemOptionsID'];

$MMColParam_rsOptions = "-1";
if (isset($_GET['TEMPIDFORRS'] )) {
$MMColParam_rsOptions = (get_magic_quotes_gpc()) ? $_GET['TEMPIDFORRS'] : addslashes($_GET['TEMPIDFORRS'] );
}
mysql_select_db($database_waConn, $waConn);
$query_rsOptions = sprintf("SELECT * FROM itemoptions WHERE ItemOptionsID = %s", GetSQLValueString($MMColParam_rsOptions, "int"));
$rsOptions = mysql_query($query_rsOptions, $waConn) or die(mysql_error());
$row_rsOptions = mysql_fetch_assoc($rsOptions);
$totalRows_rsOptions = mysql_num_rows($rsOptions);

mysql_select_db($database_waConn, $waConn);
$query_rsCategories = "SELECT * FROM itemcategory ORDER BY ItemCatName ASC";
$rsCategories = mysql_query($query_rsCategories, $waConn) or die(mysql_error());
$row_rsCategories = mysql_fetch_assoc($rsCategories);
$totalRows_rsCategories = mysql_num_rows($rsCategories);

[Travis250923]

Well I think I got it. I made the categorylink that is the relational table an inner join so it looks like this:

SELECT *
FROM itemcategorylink INNER JOIN items ON items.ItemID = itemcategorylink.ItemID
WHERE ItemCatID = colname

It seems to work but I'll have to test more to confirm.