How to acchieve this database result?

[eluser]mvdg27[/eluser]
Hi guys,

I'm stuck on creating a database result. The problem is that I have to fetch results from various tables, which makes the whole thing kind of complicated. I hope someone can help me out.

I have a table where comments on the website are stored. A comment can be assigned to any page and any section (section is for example website1, website 2). I'm storing the comments as such:

id | section_id | page_id | comment

Now I want to display a grid of all the comments. But off course I want to substitute the section_id with it's name, and the page_id with the page title. So the result should be something like

id | section_name | page_name | comment

Now I know my query is going to need some joins here. The problem is that I'm having difficulties understanding how to use them in this case, since the number of joins isn't fixed.

The section names are stored in a table 'sections':

id | section_name

And the page_name should be retrieved from a table called 'section1...x' (the id of the section is appended to 'section').

Any idea on how to go about such a thing?

Thanks in advance.

Michiel

[eluser]TheFuzzy0ne[/eluser]
I'm a bit confused by your explanation. It's probably not a reflection on you, but rather my inept ability to read and understand correctly. Please could you post the database schema for each of your tables? That should help use figure out what needs to be done.

[eluser]mvdg27[/eluser]
Hehe .. no problem. I'm sure I didn't explain it completely right either as I'm struggling with it myself as well Thanks for trying to help at least!

The following tables exist in my database:

comments:
id - INT, autoincrement
section_id - INT
page_id - INT
comment - TEXT

The combination of section_id and page_id indicates to which specific page the comment belongs to (e.g. section_id = 1, page_id = 2, refers to the page 'About us' on the English website).

sections:
id - INT, autoincrement
name - string

This table keeps track of the existing sections. For example id 1 refers to 'Website english', and 2 refers to 'Website dutch', and 3 refers to 'Photo album', etc.

Then for each section, a new table is created to store the actual content for that section (i.e. website pages, or in case of a photo album, a photo). The table is always named after the id of that section, as such: 'section1', 'section2', 'section3', etc. The exact number of columns may differ per section, but at the very least they have the following two columns:

section1:
id - INT, autoincrement
name - string

Ok, now the thing I'm after is this:

I want to create a database result of the comments:

Code:

$query = $this->db->get('comments');
$result = $query->result('array');

But instead of displaying the section_id (a number) I want to display the appropriate name of that section (Website english, photoalbum, etc). And instead of th page_id (a number) I want to display the page name (About us, photo 1, etc).

The section names will always be fetched from the table 'sections'. But the page name should be fetched from a table that is dependent of the section_id. And that last part is sort of the problem ...

I hope I explained a bit better now .. if not, I'll give it another go

Thanks again.

Michiel

[eluser]Dam1an[/eluser]
Try something along the lines of

Code:

$this->db->select('comments.comments, sections.name, page.title');
$this->db->join('sections', 'comments.section_id = sections.id');
$this->db->join('pages', 'comments.page_id = pages.id');
$query = $this->db->get('comments');
... do something with the query here

It should set you off in the right direction, but will need some tweaks no doubt

[eluser]mvdg27[/eluser]
Hi Dam1an,

You're right about getting the section name. That will work without any problem, because it's always the same table. My actual issue (I'm beginning to understand my problem more now myself as well ) is that I don't have 1 single table called 'pages'. Instead I want to look up the page name in the table 'section1' if the section_id for a row is 1, and in 'section2' if the section_id for a row is 2, and so forth ..

Thanks so far for thinking along!

Michiel