• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
my_model, queries and joins

#1
[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


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

#3
[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

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

#5
[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


#6
[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

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

#8
[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

#9
[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

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.