CodeIgniter Forums

Full Version: my_model, queries and joins
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2

El Forum

[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

El Forum

[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.

El Forum

[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

El Forum

[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.

El Forum

[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:
<?php foreach($page_content as $row) : ?>
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

El Forum

[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-gui...ecord.html

El Forum

[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...

El Forum

[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

El Forum

[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

El Forum

[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.
Pages: 1 2