MySQL Category Problem

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.

codyodell · 06-05-2008, 03:40 AM

No one has any ideas? Let me know if theres any more info about the problem I should post.

buggabill · 06-06-2008, 05:24 PM

One thing that you could do is take your array and implode it into a string.

php Code:

$strcat = implode(', ', $arrcat);

// For example say you have a sample array 
// that looks like this:
//   $arrcat = array(1, 3, 5);
// The first step will take the array 
// and implode it into $strcat like so:
// $strcat = '1, 3, 5';

Then use that string in the query for the criteria (Note the use of the IN keyword:

php Code:

$sql = '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 CA.nCategoryId IN ('.$strcat.') AND PO.bFeatured = 1';

buggabill · 06-06-2008, 05:35 PM

BTW...
I made my way over to the snipply site that I saw from your other posts. I like the idea. I am joining now.

codyodell · 06-07-2008, 09:47 PM

Thanks, I didn't think of making an array of ALL the categoryId's I needed prior to executing the query, I was trying to dynamically get the main categories and get the children categories through the IN() function.

I would be interested to see if anyone could write a recursive mysql query to search inside children categories infinitely given 1 root categoryId.

As for Snipply, I've been fixing a lot of bugs, adding new stuff. I'll keep everyone updated once I get snipply's blog up, and I should be able to sync my local code in the next week, so that we can actually start using the site (Right now, there are some major bugs making the site unusable). Thanks for signing up, anyone already signed up before I make the site invite-only will automatically get an invite. I'll also be giving out extra invites based off the bug reports/content the users submit, so make sure to send me a bug or a comment/idea if you have one with the 'report a bug' link at the bottom.