my_model, queries and joins - Printable Version

my_model, queries and joins - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: my_model, queries and joins (/showthread.php?tid=50988)

Pages: 1 2

my_model, queries and joins - El Forum - 04-16-2012

[eluser]threestate[/eluser]
good day
so, i'm trying to work with joins and my_model.. and i'm really struggling..
i have 3 tables

posts
post_id
post_title

cats
cat_id
cat_title

and

post_cat
post_id
cat_id

where the tblpost_cat is essentially a joining reference for all the posts and whatever categories they may belong to...
i can't figure out for the life of me, how to join it all together
can someone please help?

i can get all of the data out, but as the post_cat table can have multiple entries of the same post, i dont know how to just display that post once, and list the categories it belongs to

my_model, queries and joins - El Forum - 04-16-2012

[eluser]Aken[/eluser]
Use the join that returns the same post multiple times, just with different cat_id. Then go through that data and manipulate it into a format you want to use - create your own object or array or whatever that contains a single instance of post data, and a list of the categories.

my_model, queries and joins - El Forum - 04-16-2012

[eluser]threestate[/eluser]
hmmmm maybe it's because i've had a long day.. but i can't wrap my head around how i would do that

my_model, queries and joins - El Forum - 04-16-2012

[eluser]Aken[/eluser]
Loop the resulting rows and pull info as you need it. If you've already pulled the post info from the first row, don't pull it again.

my_model, queries and joins - El Forum - 04-16-2012

[eluser]threestate[/eluser]
i appreciate your help.. and maybe i'm being thick.. but..

in my controller:

Code:
$data['page_content'] = $query_content;

in my view:

Code:
&lt;?php foreach($page_content as $row) : ?&gt;

 post id = &lt;?php echo $row['post_id'];?&gt;<Br>

 title - &lt;?php echo $row['title'];?&gt;<br>

 category name - &lt;?php echo $row['name'];?&gt;<Br>

 category id - &lt;?php echo $row['cat_id'];?&gt;<br>

 <hr>

&lt;?php endforeach;?&gt;

so when i run this, if a post belongs to multiple categories, it will post multiple times.. where and how am i doing this.. say if i wanted the results to be

post id <Br>
title <br>
category name1,category name2,category name3
<hr>

etc

again, i really appreciate your help

my_model, queries and joins - El Forum - 04-16-2012

[eluser]C.T.[/eluser]
Problem Table
=====================
posts:
post_id
post_title

cats:
cat_id
cat_title

post_cat:
*id
post_id
cat_id

Code:
$this->db->select('post_cat.id AS id');

$this->db->select('cats.cat_title AS category');

$this->db->select('posts.post_title AS title');

$this->db->join('posts', 'posts.id = post_cat.post_id');

$this->db->join('cats', 'cats.id = post_cat.cat_id');

$query = $this->db->get('post_cat');

Try this?
maybe you can have a look at http://ellislab.com/codeigniter/user-guide/database/active_record.html

my_model, queries and joins - El Forum - 04-16-2012

[eluser]Aken[/eluser]
You should manipulate the data in your model, after you've retrieved it from your database. Create your own array using the data. I really don't want to write the code for that because it's pretty basic stuff...

my_model, queries and joins - El Forum - 04-16-2012

[eluser]threestate[/eluser]
I appreciate your help.
for the record.. i'm a bit of a newbie.
so, whilst it may be "pretty basic stuff" for you, it's not for me, and i'm sure a lot of others...
but thanks

my_model, queries and joins - El Forum - 04-16-2012

[eluser]threestate[/eluser]
C.T. your method was the same as my initial, but again, thanks.
I have no problem getting the data, the problem is if a post belongs to more than one category, it displays twice.
i'm still trying to find my "ah ha!" moment, but it doesnt seem to be coming

any help is appreciated

my_model, queries and joins - El Forum - 04-16-2012

[eluser]Aken[/eluser]
Then I suggest you become more familiar with basic PHP before getting too involved with CodeIgniter. Frameworks are not really meant for beginners.