Mysql:one to many and php pagination -

here's table structure

create table `cats` (   `cat_id` int(11) not null auto_increment,   `cat_name` varchar(50) not null,   `cat_status` tinyint(2) not null,   primary key  (`cat_id`),   key `cat_name` (`cat_name`,`cat_status`) ) engine=myisam  default charset=utf8 auto_increment=5 ;  --  -- dumping data table `cats` --   insert `cats` values (1, 'news', 1); insert `cats` values (2, 'sports', 1); insert `cats` values (3, 'political', 1); insert `cats` values (4, 'computer', 1);  -- --------------------------------------------------------  --  -- table structure table `posts` --   create table `posts` (   `post_id` int(11) not null auto_increment,   `user_id` int(11) not null,   `post_title` varchar(150) not null,   `post_desc` text not null,   `post_time` int(10) not null,   `post_status` tinyint(1) not null,   primary key  (`post_id`),   key `user_id` (`user_id`,`post_time`,`post_status`),   fulltext key `description` (`post_desc`) ) engine=myisam  default charset=utf8 auto_increment=3 ;  --  -- dumping data table `posts` --   insert `posts` values (1, 1, 'barcha vs r.madrid', 'football match messi vs reonaldo', 1365122983, 1); insert `posts` values (2, 1, 'web devlopment basic', 'etc etc etc etc etc etc etc etc etc etc etc ', 1365122983, 1);  -- --------------------------------------------------------  --  -- table structure table `post_relation` --   create table `post_relation` (   `post_id` int(11) not null,   `relation_type` varchar(10) not null,   `with_id` int(11) not null,   key `relation_type` (`relation_type`,`with_id`,`post_id`) ) engine=myisam  default charset=utf8;  --  -- dumping data table `post_relation` --   insert `post_relation` values (1, 'cat', 1); insert `post_relation` values (1, 'cat', 2); insert `post_relation` values (2, 'cat', 4); insert `post_relation` values (1, 'tag', 1); insert `post_relation` values (1, 'tag', 4); insert `post_relation` values (1, 'tag', 5); insert `post_relation` values (2, 'tag', 6);  -- --------------------------------------------------------  --  -- table structure table `tags` --   create table `tags` (   `tag_id` int(11) not null auto_increment,   `tag_name` varchar(50) not null,   `tag_status` tinyint(2) not null,   primary key  (`tag_id`),   key `tag_name` (`tag_name`,`tag_status`) ) engine=myisam  default charset=utf8 auto_increment=7 ;  --  -- dumping data table `tags` --   insert `tags` values (1, 'football', 1); insert `tags` values (2, 'usa', 1); insert `tags` values (3, 'war', 1); insert `tags` values (4, 'messi', 1); insert `tags` values (5, 'ronaldo', 1); insert `tags` values (6, 'php', 1); 

sqlonline: http://sqlfiddle.com/#!2/9d20d/8

and i'm trying fetch posts cats , tags

with sql query

select       posts.*, cats.cat_name catname , tags.tag_name tagname       post_relation inner join        posts on ( post_relation.post_id = posts.post_id ) left join       cats  on ( post_relation.with_id = cats.cat_id , post_relation.relation_type = "cat"  ) left join       tags  on ( post_relation.with_id = tags.tag_id , post_relation.relation_type = "tag"  ) 

i want result array

$posts = array(     array(        "post_id" => 1 ,        "user_id" => 1 ,        "post_title" => "barcha vs r.madrid" ,        "post_desc" => "football match messi vs reonaldo" ,        "post_time"=>  1365122983 ,        "post_status" => 1 ,        "post_cats"  => array(                           "1" => "news" ,                            "2" => "sports"                         ) ,        "post_tags"  => array(                           "1" => "football" ,                            "4" => "messi" ,                           "5" => "ronaldo" ,                        )      ) ,     array(        "post_id" => 2 ,        "user_id" => 1 ,        "post_title" => "web devlopment basic" ,        "post_desc" => "etc etc etc etc etc etc etc etc etc etc etc " ,        "post_time"=>  1365122983 ,        "post_status" => 1 ,        "post_cats"  => array(                            "4" => "computer"                         ) ,        "post_tags"  => array(                           "6" => "php" ,                        )       ) ) 

i can array in php no problems but

in example have 2 posts

but query returns 7 rows

if query works pagination 5 rows

my array lose tag in post 1 tag in post 2

and in page number 2 show lost tags

how can query , full array perfect pagination

how optimize query too

right sql query returning this:

<?php      $posts = array(         array ('post_id' => 1, 'user_id' => 1 ...),         array ('post_id' => 1, 'user_id' => 1 ...)     );  ?> 

with new row every single 'catname' , 'tagname' column.

i recommend using separate queries when building php array, example:

select * posts 

then each iteration of posts, query array require, example:

select   cats.cat_name     post_relation, cats    post_relation.post_id = 1          , relation_type = 'cat' 

this array categories, , tags.

mysql not have way return array within column, if want single query, might have concat subquery let php split results array using csv or something. not recommended either way.