Ordering blocks

owainhunt

On my site (currently at http://www.zero.uk.co), I have a block system for things such as navigation, affiliates, headlines etc, much like that on deskmod.com, and I'm working on making the blocks configurable in terms of their positions on the page. I've run into a bit of difficulty however when it comes to displaying blocks in the correct order. Here's what I've got so far:

Each user's configured block details are held in a mysql table with the format (id, user, table1, table2, table3, etc), and each tablex field holds the data (state|column|position) where state is either minimized or maximizied, column is left or right and position is a number reflecting the block's position in the column, with '1' being at the top).

This is where the problem comes: how do I code so that the block with position 1 comes first, then pos 2, and so on??

(Cripes, that was a bit long-winded wasn't it?!)

Anon

One possibility is to ORDER BY SUBSTRING_INDEX(position, '|', -1). To fetch out the state it would be SUBSTRING(position,'|,1). The column bit will be hairier.

It could also be done in PHP itself, using usort() and maybe create_function(), but that requires all the blocks you'll be sorting being read into the one array so that PHP can sort them.

An alternative PHP solution still requires reading in all the blocks, but identifying their position first, calling it $position, and then shovelling the block specification into an array element $blocks[$position].

But ultimately I still think you'd be happier rethinking the database structure; having a "block" table with fields (id,userid,state,column,position) and reducing user to (id,user) and whatever additional fields you keep. If your users are [id,user]=[1,tom][2,dick][3,harry] then harry's blocks can be found with 'SELECT * FROM blocks WHERE userid=3 ORDER BY position'. Consider what would happen otherwise if you wanted to have the blocks colour-coded...