How to combine two rows into one in mysql query?

[eluser]vadimyer[/eluser]
Hello there.

I have a SEARCH query (that contains LIKE condition and JOIN to join tags table) that returns this array:

Array ( [0] => stdClass Object ( [title] => First post [tag] => cats ) [1] => stdClass Object ( [title] => First post [tag] => dogs ) )

And when I foreach() it in PHP, it returns 2 same results, but first result contains the tag from [0] and the second - from [1].

How can I print only one result containing both two tags? Cause it is the same "first post" but because of tags the query returns me two array objects.
Also notice that tags should be html-formatted and contain links, so mysql GROUP_CONCAT is not the solution.

Thanks in advance!

[eluser]BrokenLegGuy[/eluser]
I think what you're looking for is the GROUP BY function in MySQL. Below is the active record syntax.

Code:

$this->db->group_by("title");

- Ed


*edit* I'm suggesting a straight GROUP BY function not aggregated function, which you said you've tried.

[eluser]vadimyer[/eluser]
Ed, yeah thanks. But that only extracts one result with the first tag, so the second tag gets lost. I need to get all tags connected to one post, also every tag has its own properties like URI etc.

Is there a way to make an array of tags with their fields, and this array would be inserted in main mysql query array?

[eluser]BrokenLegGuy[/eluser]
[quote author="vadimyer" date="1310763736"]Ed, yeah thanks. But that only extracts one result with the first tag, so the second tag gets lost. I need to get all tags connected to one post, also every tag has its own properties like URI etc.

Is there a way to make an array of tags with their fields, and this array would be inserted in main mysql query array?[/quote]

Oh ok, I get what you're looking for now. I'll look though some code and see if I've got something like that some where, not sure if I do though. I'll let you know.

-Ed

[eluser]danmontgomery[/eluser]
GROUP_CONCAT() will concat a field based on the GROUP BY statement, but it will be a string. There are no arrays in mysql, so you will have to explode the result.

Code:

$query = $this->db
    ->select('posts.post_id, posts.title, GROUP_CONCAT(tags.title SEPARATOR ",") AS tags', NULL, FALSE)
    ->join('tags', 'tags.post_id = posts.post_id', 'left')
    ->where('posts.post_id', 5)
    ->group_by('posts.post_id')
    ->get('posts');

if($query && $query->num_rows())
{
    $post = $query->row();
    $post->tags = explode(',', $post->tags);
}

http://dev.mysql.com/doc/refman/5.0/en/g...oup-concat