Is it possible to pass a variable from a view to a controller?

[eluser]developer10[/eluser]
[quote author="Haloperidol" date="1246966927"][quote author="cold_fusion" date="1246932495"][quote author="Haloperidol" date="1246925705"]yup, its because you have to put the other table first (on the left side), try this:

SELECT * FROM tbl_djelatnosti left join (SELECT count(f_id) as count, d_id from _tbl_firme group by d_id) as foo on tbl_djelatnosti.d_id = foo.d_id[/quote]


this change does the trick, the missing category gets listed, but has nothing in brackets, like this (). normally, there should be (0) if there's no
companies associated with its id. any solution?[/quote]

well, you can always check $row->counter in the view's foreach loop and display "0" if its empty.[/quote]


aah, i will try to do that, thanks for suggestion!

[eluser]developer10[/eluser]
[quote author="sl3dg3hamm3r" date="1246968301"][quote author="cold_fusion" date="1246932852"][quote author="sl3dg3hamm3r" date="1246925996"]didn't look well at the query. Yes, 'inner' wouldn't change much. I would formulate the query different (not tested!):

Code:

SELECT dje.djelatnost, count(firme.d_id) AS firmCount
FROM tbl_djelatnosti AS dje
LEFT JOIN tbl_firme AS firme ON dje.d_id = firme.d_id
GROUP BY dje.d_id

It is way more readable like that, subquery or derived table is not necessary...[/quote]


well, it seems you included some fields that do not exist in my table

above i posted how my tables look like so you have needed field names

category table: tbl_djelatnosti, with only 2 field names: d_id and djelatnost

company table: _tbl_firme, needed fields are: f_id and d_id

here's the live example of what im trying to accomplish, link is in my signature

that's the site im trying to rebuild using CI, currently it's coded in standard PHP[/quote]

I don't think so. All fields in my example do exist (don't mix up aliases with the physical field-/table-names). You can adjust the selected fields according to your needs. Did you try at all?[/quote]

yes, i did give it a try, and it threw me an error message (dont remember which one, but certainly it was an sql error)

i think it's because an alias "dje" appears in the statement BEFORE it's defined ( SELECT dje.djelatnost, count... )?

[eluser]sl3dg3hamm3r[/eluser]
Your need is really quite simple and belongs into the DB-query as I showed, instead of blowing up your code with if's... did u try it?

[eluser]sl3dg3hamm3r[/eluser]
hmm, at least with ms-sql this would work. right now i don't have a mysql to try out. the error-message might be helpfull.

and no, alias are defined like that.

[eluser]developer10[/eluser]
[quote author="sl3dg3hamm3r" date="1246972090"]Your need is really quite simple and belongs into the DB-query as I showed, instead of blowing up your code with if's... did u try it?[/quote]

i did it finally! all the time i was using $row->count (as in Haloperidol's solution) to display number in my view, but finally i saw that firmCount, used that and that was it!

Thank you very much, sledge!

[eluser]sl3dg3hamm3r[/eluser]
Ah yeah, sorry. I renamed the aggregate with an alias, what I always would suggest. Glad it worked out
You, of course, could rename it after your personal preferences...

[eluser]developer10[/eluser]
[quote author="Haloperidol" date="1246966927"][quote author="cold_fusion" date="1246932495"][quote author="Haloperidol" date="1246925705"]yup, its because you have to put the other table first (on the left side), try this:

SELECT * FROM tbl_djelatnosti left join (SELECT count(f_id) as count, d_id from _tbl_firme group by d_id) as foo on tbl_djelatnosti.d_id = foo.d_id[/quote]


this change does the trick, the missing category gets listed, but has nothing in brackets, like this (). normally, there should be (0) if there's no
companies associated with its id. any solution?[/quote]

well, you can always check $row->counter in the view's foreach loop and display "0" if its empty.[/quote]

man, thanks for your help, your last suggestion also worked nicely.
however, it turned out that sledgehammer was right - it was possible to get all I needed with his query, and it even displayed 0 for that empty category

Problem is now solved, and if you like, check my other topics regarding rebuilding my site in CI!

Best regards,

Denis