Help in foreach inside foreach.

[eluser]Sinclair[/eluser]
Hi, I need help for listing this in a RSS feed...

The feed will contain information about The blog-post and the tags associated to the post. I have the information from two tables,
One with the posts and the other with the tags.

TABLE "POSTS"
id_post | n_post | description
1 | aaaa | aaa aaa aaa aaa
2 | bbbb | bbb bbb bbb bbb
3 | cccc | ccc ccc ccc ccc

TABLE "TAGS"
id_post|id_ordinal|n_tag
1 | 1 | tag1
1 | 2 | tag2
1 | 3 | tag3
2 | 1 | tag1
2 | 2 | tag2
3 | 1 | tag1
3 | 2 | tag2
3 | 3 | tag3

I have the idea that I must to do two foreach cicles. But I'am with doubts in the query(SQL code) that I need to do...

Code:

<?php foreach($bloposts as $post) : ?>
    <?php foreach($posttags as $tags) : ?>
        <?php echo $tags->n_tag; ?>
    <?php endforeach; ?>
    
    <?php echo $post->n_post . '-' . $post->description; ?>

<?php endforeach; ?>

Do tou have any ideas on how to do this?


Best Regards,

[eluser]Rob Gordijn[/eluser]
my approach to this (did it a few weeks ago)

1) select your posts
2) with a while($row = $q->row()) [or something like it] build up an array $posts
like: $posts[$row->id_post] = $row; so you got all the data from the post in that array, and the key is the post_id
3) create a array for the tags: $posts[$row->id_post]['tags'] = array();
4) make a select on the tags, use "WHERE `post_id` IN (implode(',',array_keys($posts))"
5) loop through the results, and assign the incoming tag_id's to $posts[$row[post_id]]['tags'][] = $row['tag_id']

I'll hope this makes sense to you

[eluser]danmontgomery[/eluser]

Code:

SELECT posts.id_post, posts.n_post, posts.description, GROUP_CONCAT(DISTINCT tags.n_tag ORDER BY tags.id_ordinal ASC SEPARATOR ', ') AS tags
FROM posts
LEFT JOIN tags ON tags.id_post = posts.id_post
GROUP BY posts.id_post

One query, one result set, all of the information

[eluser]Sinclair[/eluser]
Hi,

Thanks for the reply. Can you show a more detailed code?

Best Regards,

[eluser]Rob Gordijn[/eluser]
[quote author="noctrum" date="1265315426"]One query, one result set, all of the information[/quote]

hmz, I need to look into more sql functions.
This is going to cut down my queries with 50% to 60% (-:

[eluser]danmontgomery[/eluser]

Code:

$query = $this->db->select("SELECT posts.id_post, posts.n_post, posts.description, GROUP_CONCAT(DISTINCT tags.n_tag ORDER BY tags.id_ordinal ASC SEPARATOR ', ') AS tags", false)->join('tags', 'tags.id_post = posts.id_post', 'left')->group_by('posts.id_post')->get('posts');
foreach($query->result() as $row) {
  // $row->id_post
  // $row->n_post
  // $row->description
  // $row->tags
}

[eluser]Sinclair[/eluser]
[quote author="noctrum" date="1265315871"]

Code:

$query = $this->db->select("SELECT posts.id_post, posts.n_post, posts.description, GROUP_CONCAT(DISTINCT tags.n_tag ORDER BY tags.id_ordinal ASC SEPARATOR ', ') AS tags", false)->join('tags', 'tags.id_post = posts.id_post', 'left')->group_by('posts.id_post')->get('posts');
foreach($query->result() as $row) {
  // $row->id_post
  // $row->n_post
  // $row->description
  // $row->tags
}

[/quote]

Great! I need to find a function like GROUP_CONCAT to use in PostgreSQL.