Query problem to get recent rows

[watsmyname]

Hello

 

I m having problem writing a query.

 

Table 1 post_index_table => this table stores the information about the posts posted in other table. The table structure and data goes likes this.

 

item_id      table_name              post_id    member_id      posted_for      posted_for_id    post_status

1                  sub_restaurant        24            13                  everyone                                        1

2                  sub_restaurant        26            11                  everyone                                        1

3                  sub_icecream            12            23                  everyone                                        1

 

The table_name holds the name of table where the items are posted. These tables are generated dynamically from backend...so we will not know the table name. Each different table might have different fields.

 

Structure of sub_restaurant

post_id    img1_img      coupon_type      file_img    coupon_no        description      title      location_type  location_id      post_status

24              abc.jpg              print                                12                  lorem ipsum    test        county              120                    1

26              xyz.jpg              digital                              15                  lorem ipsum    test        state                123                    1

 

and so on for other tables. though structure of tables might be different, all tables have post_id, location_type, location_id and post_status common.  Here location_type field determines whether the post is posted on county, state, city or country and location_id field holds respective id. The post should be shown only from the location the user is signed in. For example if i m from Palm Beach county User should be able to recent posts posted in Palm beach county only.

 

My problem is... I want to select 3 most recent post depending upon the location. the user browses the site. And when user clicks more then next three will be shown. I know we first have to query index table post_index_table to get table name to fetch posts, but i m not being able to write query.

 

Things I have done

1. I looped through index table first with limit 0,3 to get max item_id it returned 3 table names,  then in loop I queried through other tables to fetch one latest from each 3 tables.

2. First two table returned the post, because it has the post in my location. But third table returned from First query didnt returned any because there is no post in my location. So first loop with limit 0, 3 show only 2 posts.

3.  I click more to limit through 3,3, again it returned say 3 other tables...  and this time all three tables has posts in it. so in second loop it shows 3 posts.

4. It seems not logical because first loop show 2 posts and second loop shows 3 posts. I want to show 3 posts all the time..may be the way i m doing is wrong.

 

Can anybody please help me? Help much appreciated.

 

Thanks

Watsmyname

[Muddy_Funster]

Could you post up your code for the full queries please?

[watsmyname]

hey thanks for the reply

well query to return records from index table

 

SELECT item_id as maxid, post_id,table_name,item_id FROM post_index_table WHERE item_id IN (SELECT MAX(item_id) FROM post_index_table GROUP BY table_name) AND (posted_for='everyone') ORDER BY maxid DESC LIMIT 0,3;

 

and within loop of above query, i queried corresponding tables to return 1 latest post.

 

SELECT mt.* FROM sub_restaurant mt, post_index_table mc WHERE mt.post_status='1' AND mt.location_type='country' AND mt.location_id in(SELECT country_id FROM cities_table WHERE city_id='212') AND (mt.post_id=mc.post_id AND mc.table_name='sub_restaurant' AND mc.posted_for='everyone')  ORDER BY mt.classified_id desc limit 0,1

 

where cities_table has the list of cities and states, county, country associated with the city.

 

for problems i m facing please view my first post