Search the Community

I’m writing a small application that allows me/users to add their own inputs so they can store their own data. It’s kind of like, a user defined database. Think of a super simple “filemaker” kind of application. The way I’ve set this up is like this: Table: item fields: item_id, user_id Table item_attribute Fields: item_attribute_id, item_id, name, value After my query I end up with stuff like this: $data = array( 0=>array( '123'=>array( 'name'=>'Year', 'value'=>'1985' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1985' ), '25'=>array( 'name'=>'Length', 'value'=>'60' ) ), 1=>array( '123'=>array( 'name'=>'Year', 'value'=>'1990' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1990' ), '25'=>array( 'name'=>'Length', 'value'=>'44' ) ), 2=>array( '123'=>array( 'name'=>'Year', 'value'=>'1965' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1965' ), '25'=>array( 'name'=>'Length', 'value'=>'122' ) ) ); This seems to work great as there is no limit to what a user can add - it's working like I would like it to... But now that I’m trying to add sorting/filtering/pagination to results things have gotten extremely difficult on the back end. The only thing I can think of is to pull all the data and then use php algorithms to sort/filter/paginate before sending over to web page. I can see this getting slow if I eventually have to pull 1000s of records. Does anyone have advice in these matters? Should I rethink my db design? Can the results be used to create a temporary table and then sort on it? (don'n know how or if this is even possible) I need some help Thanks.