Codeigniter - how to use the foreign keys from one table to look for the id's of another table.

Question

I'm having a problem trying to wrap my head around how to query the database to retrieve the foreign keys in my Nav table and then using those to find the id's in the NavItem table. Can someone point me in the right direction or give me some pointers? i'm not even sure if this should be done in the model or the controller!

Thanks in advance

EDIT

My NavItem table looks like this:

id, button1, button2, button3, button4, region. (the buttons are all foreign keys leading to a separate buttons table)

Answer 1

You have to either query the buttons table four times, once for each of button1-button4, or you have to join the button table four times in your select from NavItem.

In the former case, your model would have two functions - a select from NavItem, and a select from buttons. You call the select from NavItem function from your controller, and use the data it returns to run the select from buttons function four times, also from the controller.

In the latter case, you only have to have one model function that runs one query, albeit not a trivial one.