Need help to retrieve some datas between tables in one query [SOLVED]

[eluser]Ludovic-r[/eluser]
Hi !

I'm stuck with something that I can't fix and need your help, let me explain my configuration :

I have 3 TABLES :

TABLE #1 : called post
TABLE #2 : called categories
TABLE #3 : called post_categories

In my TABLE #1 I have some fields like id, text, title, date and so on
In my TABLE #2 I have id and category_name
In my TABLE #3 I link the post_id with the category_id

This is for the MySQL part.

Now I would retrieve my TABLE #1 entire informations and link my TABLE #2 category_name informations within the post
So to do that I've created TABLE #3 which make the link between post and category

The problem is that I can't retrieve my informations I don't know why :/

I've tried that :

Code:

$this->db->select('*');
        $this->db->from('post');
        $this->db->join('categories', 'post_categories.post_id = post_categories.category_id');
        $this->db->where('post.id', 'post_id');
        $this->db->order_by('rank', 'asc');
        
        $q = $this->db->get();
        
        return $q->result();

Oh and one another thing, If I need to DELETE once the category_name and the occurence in the TABLE #3 is it possible? I heard something like ON CASCADE but don't really know how to use it.

Any idea and help would be very very appreciated!

Thanks! (Sorry for my poor english)

[eluser]Krzemo[/eluser]
Seems like you need some reading on databases:
http://dev.mysql.com/tech-resources/arti...ation.html
http://dev.mysql.com/doc/refman/5.5/en/i...aints.html

Your AR code is totally wrong. I'd suggest to write regular query in MySQL Query Browser or any other tool of you choice and then move it to CI AR.

[eluser]Ludovic-r[/eluser]
I know it's wrong, that was an idea. I've played many times with AR with success I'm just stuck on this thing.

[eluser]Krzemo[/eluser]
It looks like regular many-to-many relation. Is the problem in query of transforming into AR code?

[eluser]Ludovic-r[/eluser]
Yes it's a many-to-many relation that I've made with TABLE A and TABLE B linked through TABLE C. It's usefull for updating and so on but now I can't retrieve a data of TABLE A in relation with TABLE B thanks to TABLE C.

In TABLE C I store the TABLE A id and TABLE B id in one row.

I'm just lost in my AR query but the SQL querry is not so hard.

Code:

SELECT c.a_id, c.b_id, b.b_desc
FROM c
LEFT JOIN b ON (b.b_id = c.b_id)
WHERE (c.a_id = something')

And I would like to know if it's the good solution to make a manytomany relation for my problem?

Thanks

[eluser]Krzemo[/eluser]
Voila

Code:

SELECT post.*, categories.*
FROM post
JOIN post_categories ON post_categories.post_id = post.id
JOIN categories ON categories.id = post_categories.category_id

[eluser]Ludovic-r[/eluser]
Ok thanks, it works but with my result now how can I show in the page the category_name row associated from TABLE A ?

Like displaying rows from the both TABLES ?

[eluser]Ludovic-r[/eluser]
Argh, I feel stupid, I've made a mistakes on the name of the row sorry...

It works like a charm now!!! Thanks!