excluding a category from a list

[Merdok]

Hi Guys,

 

I've been using PHP for a few years now but for some reason I can never seem to get my head around arrays, basically I have a blog site that I want to show all posts that have been posted in public categories but ignore those in the private categories, I had the idea to put all the private category ID's into an array and use that to check against the posts, however my array isn't working out the way I'd hoped.

 

This is what I have:

 

$isPrivate = mysql_query("SELECT categoryID FROM mod_cat WHERE isPrivate =1") or die (mysql_error());
while ($isPrivateList = mysql_fetch_array($isPrivate, MYSQL_NUM)) {
$ignoreList = $isPrivateList;
}

 

However this isn't working at all. I know its wrong I just can't work out how to make it right. This is the mySQL I want to use it with:

 

mysql_query("SELECT COUNT(*) AS total_entries FROM mod_posts WHERE articlePosted =1 AND articleCat != $ignorelist")

 

Could anyone please help me?

 

Thanks in advance.

[sasa]

SELECT COUNT(p.*) AS total_entries FROM mod_posts AS p  
LEFT JOIN mod_cat AS c ON c.categoryID = p.articleCat
WHERE p.articlePosted =1 AND c.isPrivate != 1

[Merdok]

LOL thanks.... I'm rubbish at joins as well... I'll give that a try later.

[Merdok]

hmm... that didn't work, I'm getting this error:

 

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 '*) AS total_entries FROM mod_posts AS p LEFT JOIN mod_cat AS c ' at line 1

 

This is how I used it:

 

$blogCounter = mysql_query("SELECT COUNT(p.*) AS total_entries FROM mod_posts AS LEFT JOIN mod_cat AS c ON c.categoryID = p.articleCat WHERE p.articlePosted =1 AND c.isPrivate != 1") or die(mysql_error()); $row = mysql_fetch_row($blogCounter); $total_entries = $row[0];

[the182guy]

Syntax error here

 

FROM mod_posts AS LEFT JOIN

 

Change to

 

FROM mod_posts AS p LEFT JOIN

 

[Merdok]

Still getting the same error. Could it be that the p.* has been referenced before it has been created?

 

Thanks by the way, that would no doubt have caused further headaches later

[the182guy]

Try changing it to the Primary Key of your mod_posts table, example

 

SELECT COUNT(p.id) AS total_entries

[Merdok]

That's a bingo Thanks!