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?!)

[[deleted]]

Do you really store delimited strings in your database columns? (hope not, that's quite bad)

'Normally' you'd just select the block data ordered by whatever order you want. In your case you'd order them by 'position'

SELECT blockdata FROM table WHERE userid=userid ORDER BY position ASC.

[owainhunt]

Whats wrong with delimited strings in dbases?

And how would that solution work with the setup I have? The way the 'base is set up mean there is no 'position' field. To do it like that there would have to be a 'blockname_column' and 'blockname_position' for each block and that seems a bit cumbersome. Is there no other way of doing it? If not I guess I'll have to change the system I'm using.

[[deleted]]

"Whats wrong with delimited strings in dbases?"

Q: Why is the string delimited?
A: because you want to get three seperate pieces of information from it.

Q: What does a database do?
A: Give you seperate pieces of information.

See my point? :-) When you use a delimited string, you are just combineing three database columns into one, which doesn't make much sense, because you cannot order by any of the combined data, and selecting rows where the combined data has 'foo' in the second field is too complex to think about.

It is mucc better to split the delimited data into their own columns.

"To do it like that there would have to be a 'blockname_column' and 'blockname_position' for each block and that seems a bit cumbersome"

That would be cumbersome, and that's why they thought of normalization.
Instead of having one table that tries to contain everything, create two tables.
One to contain the user data,
and one to contain block data:

users:
id,name,whateverelseyouneed

blocks:
id,userid,blockname,position,state,color,column,whatever

Now you'd create a new recod in the blocks table for every block that the user can use. Ten blocks per user=>ten records in the blocks table per user.

Then you can get all blocks for the left column like this:

SELECT * FROM blocks WHERE userid=$userid AND column=1 ORDER BY position;

and for the second column:

SELECT * FROM blocks WHERE userid=$userid AND column=2 ORDER BY position;

Get all collapsed blocks for the first column:
SELECT *
FROM blocks
WHERE userid=$userid
AND column=2
AND state='collapsed'
ORDER BY position;

[owainhunt]

Thanks 🙂