TalkPHP - View Single Post

codyodell · 06-03-2008, 06:23 PM

Hey guys, I'm building a 2-level menu system. My category table has nCategoryId, nParentCategoryId, and strCategory. I am joining this table with my table of posts. When a user clicks a main category I want them to see only the featured posts (tblposts has a boolean flag for featured) for the current category and ALL of it's children.. and I only want to use one query.

Here is what I have currently:

Code:

SELECT PO.strTitle, PO.bFeatured, PO.strPreview, PO.strFile, PO.dateCreated AS tsCreated, DATE_FORMAT(PO.dateCreated, '%b, %D %Y %l:%i %p') AS dateCreated, PO.nPostId, PO.nPostType, PO.strPost, PO.strAbout, PO.nViews, CA.strCategory, CA.nCategoryId, CA.nParentCategoryId FROM tblposts PO INNER JOIN tblcategories CA ON CA.nCategoryId = PO.nCategoryId WHERE 1 = 1 AND PO.bFeatured = 1

Say I have an array of all the category ID's I want to show, how would I incorporate that into this query to only show featured posts for these categories? I am open to different approaches, but I already have the array setup. I've also tried using IN(), but had no luck either.

06-03-2008, 06:23 PM	#1 (permalink)
codyodell The Wanderer Join Date: Nov 2007 Posts: 12 Thanks: 2	MySQL Category Problem Hey guys, I'm building a 2-level menu system. My category table has nCategoryId, nParentCategoryId, and strCategory. I am joining this table with my table of posts. When a user clicks a main category I want them to see only the featured posts (tblposts has a boolean flag for featured) for the current category and ALL of it's children.. and I only want to use one query. Here is what I have currently: Code: SELECT PO.strTitle, PO.bFeatured, PO.strPreview, PO.strFile, PO.dateCreated AS tsCreated, DATE_FORMAT(PO.dateCreated, '%b, %D %Y %l:%i %p') AS dateCreated, PO.nPostId, PO.nPostType, PO.strPost, PO.strAbout, PO.nViews, CA.strCategory, CA.nCategoryId, CA.nParentCategoryId FROM tblposts PO INNER JOIN tblcategories CA ON CA.nCategoryId = PO.nCategoryId WHERE 1 = 1 AND PO.bFeatured = 1 Say I have an array of all the category ID's I want to show, how would I incorporate that into this query to only show featured posts for these categories? I am open to different approaches, but I already have the array setup. I've also tried using IN(), but had no luck either. __________________ The man who invented the wheel was smart. The man who invented the other 3, he was a genius.