Page 1 of 1

Ordering numerically not alphabetically...

Posted: Tue Oct 31, 2006 7:21 am
by Skittlewidth
I anticipate a problem coming up with a client any minute now....

I created a system for them to create tables for product data.
They input size infomation in the format "10mm" or " 3' 3" " which requires the col type to be varchar.
When the data is output in html they want users to be able to click on the table headers to order results by that column, and although that in itself is easy, they're going to complain when they notice values like "100mm" appearing above "10mm".

I can't change the column type to "float" or "int" because they're going to want to put those unit values in.
Anyone got any ideas how I can sort these columns numerically, perhaps by creating some kind of 'float' alias when I query the database? They idea is half there in my head but it won't quite formulate fully!

Posted: Tue Oct 31, 2006 7:37 am
by CoderGoblin
The easiest solution I can think of is to insert another column of "true_val" which is just a float. When inserting/update into the database process the text string and convert to a common size(meters ?) and store this value. You then sort on this value even if you don't output it.
This would also sort things according to imperial/metric (6'4<2m) correctly.

Of course this would mean validation of the original text is required so conversion can work.

Posted: Tue Oct 31, 2006 8:09 am
by Skittlewidth
Good idea in theory (this was the sort of idea that was beginning to form in my head) but here is the snag... the tables don't really exist.

The client needed to create his own product tables (100s potentially 1000s of them) as no two products have the same table structure, ie one might simply have the headers "Part No", "Size", "Length", another could have 10 different headers depending on the type of component it was for.

He needed the freedom to create new table headers as he pleased, so the system allows him to create a product group table, assign various headers to that table but all that get stored in the database at this point is his table name and the ids of the associated columns.

Storing values in this non existant table gets even more fun. Values are stored in another table with the associated column id they belong to, the table it belongs to and the row number it is from... phew. Another reason why all the data is in a varchar type field.

Anyway when the time comes to display all this data to a website user I do create a temporary table in MySQL to assemble the data into to make queries easier.
I guess if I perform some regex on the data before I create the table to see if the first x characters were numeric I could create a float columns in this temp table to sort by and inject only the numeric part of the data into them. But the system needs to be able to associate that float column with the particular varchar column that it is acting for.

Given that the system is essentially blind to the whole table in that it can't guarantee anything about its structure, this would be the tricky part I think. Sorts are currently performed using col numbers eg. "order by 3". Hmmm.... another half formulated idea is coming on....

Posted: Tue Oct 31, 2006 9:50 am
by CoderGoblin
I know you unlikely to want to change your current method but for anyone else looking at this post without an existing system...

What you are saying is that different "product types" have different "attributes". The way I have solved this in the past is to use database tables simplified as follows.

Code: Select all

Table:ptype
Columns:ptype_id | short_desc | long_desc

Table: attribs
Columns:attrib_id | short_desc | long_desc | validation_method

Table: ptype2attrib
Columns ptype_id | attrib_id | is_required

Table: product2attr
Columns: product_id | attr_id | attr_val

Table: product
Columns: product_id | ptype_id | etc..
Ok needs admin for the creation of product types/creation of products but this approach works and has saved us lots of problems. We have a customer using a "shop system" for artist materials where there are about over 10000 products with about 50 "product types". In our final system, the selection of which product type/attributes is simplified as they are based in treelike categories where we can actually inherit attributes when creating products.

Posted: Tue Oct 31, 2006 11:02 am
by JayBird
Yes, as CoderGoblin says, that is the way you should have done it, a one-to-many relationship.

I don't know, but sometimes it is worth taking a step backwards, to go forwards.