[musiccreatures]

I had several requests to ad a count of ads within each category. I figured, my best bet would be to adjust the SQL that gathered the active categories. The issue is, that if the category does not have any ads (active or not active) then the category doesnt get displayed.

The troubling part is, I realize that the reason it isn't returning any ads, is because of all the conditions that need to be met, for an ad to be counted. Does anyone have an idea on how to get this SQL to return ALL Categories that meet the categories conditions, as well as a count of ads within each category, even if the count is 0?

Code

Perfectly good SQL went here!

Any suggestions would be greatly appreciated,
Tim



Last edited by musiccreatures on 11/23/09 22:45; edited 1 time in total

[Michael]

Without having phpmyadmin infront of me to play with, i find it hard to build queries in my head, but could you use a case/switch statement.

select *,
case( when 'category' = 1 then 1 else 0 end) as count,


_________________
Michael Ussher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[musiccreatures]

Whoa! I didn't realize I could use switch statements in mysql. Thanks for the advice, I'll look into it. Thanks again ussher.

-Tim

[Michael]

if you want another set of eyes on it, pm me access to the phpmyadmin and ill have a play too if you like. or the SQL file and ill load it locally.


_________________
Michael Ussher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[musiccreatures]

lol....wouldn't you know it. Had nothing to do with the SQL. I had an issue with the schema.php file where I left a comma where there shouldnt have been one. so the ads table wasn't created. Works like a champ now.

Thanks for the offer though.

[Michael]

Nice one! This still counts as bribery for next time I get stuck though.


_________________
Michael Ussher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[musiccreatures]

its a deal