inner_join?

hey Jason

Not sure if using a join is even the right way to go, but have been trying to use this on a page which lists products which have been filtered by the category and I wish to show the category name at the top of the page.

I have 2 tables - productcategories and products - and the content is being populated using the products table and being filtered by the ProductCategoryID with the url parameter of CategoryID (which is in the productcategories table.

So, am trying to get the CategoryName (from the productcategories table) to show as the <h1> on the page and have tried using a variety of differnt tries including (and at present) this

mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_Category = "SELECT CategoryName, products.ProductCategoryID FROM productcategories INNER JOIN products ON ProductCategoryID = CategoryName";
$Category = mysql_query($query_Category, $PowerCMSConnection) or die(mysql_error());
$row_Category = mysql_fetch_assoc($Category);
$totalRows_Category = mysql_num_rows($Category);

Clearly its not working and am getting myself all tied up trying to figure it out.

Thanks in advance

Hi

You need to include the CategoryID from your categories table in the query...
Try something like this:

mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_Category = "SELECT CategoryID, CategoryName, products.ProductCategoryID FROM productcategories INNER JOIN products ON ProductCategoryID = CategoryID";
$Category = mysql_query($query_Category, $PowerCMSConnection) or die(mysql_error());
$row_Category = mysql_fetch_assoc($Category);
$totalRows_Category = mysql_num_rows($Category);

many thanks Willi... really appreciate your help

I see what I missed out..thanks. The only thing is that although the category name is now showing, it is only showing the 1st record regardless of which page it is on

you don't have a where clause in the recordset to filter which record to show.

I have tried

$query_Category = "SELECT CategoryID, CategoryName, products.ProductCategoryID FROM productcategories INNER JOIN products ON ProductCategoryID = CategoryID WHERE CategoryID = %s";

and that gives me "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1"

I also tried

$query_Category = "SELECT CategoryID, CategoryName, products.ProductCategoryID FROM productcategories WHERE CategoryID = %s INNER JOIN products ON ProductCategoryID = CategoryID ";

which gave me "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s INNER JOIN products ON ProductCategoryID = CategoryID' at line 1"

what is the url variable that the link passes in?

athome.php?CategoryID=6

dont edit the code, edit the recordset and use the following SQL:

SELECT CategoryID, CategoryName, products.ProductCategoryID FROM productcategories INNER JOIN products ON ProductCategoryID = CategoryID WHERE CategoryID = paramCatID

variables:
name: paramCatID
type: integer
default value: -1
Run Time value: $_GET['CategoryID']

stupendous Jason Many thanks