Data Type Dilemma

walterbennet

Having some trouble with data types... In my script, I need the table's data to be ordered by "page", but the values have mixed char/num data (for instance, "21-22"). Because of the hyphen, I have to set it to VARCHAR (or it will erase the hyphen completely), but since it's a number, it won't order properly.

For example, let's say my page numbers are 1, 21-22, 31, 121.

SELECT * FROM 'table' ORDER BY page;

Would return: 1, 121, 21-22, 31 (it's ordering by the first digit in the value only).

Is there a datatype that will allow me to include hypens, but still order properly as numeric data?

Thanks in advance guys!

laserlight

Comma separated value fields often point to some problem in the database design. What are you trying to model, i.e. what is the table?

walterbennet

Well, they aren't really comma seperated values, I just did that for the purpose of the example. The values exist in a table, and can be viewed here, after you fill out the form (which you helped me create 🙂

http://www.skullknight.net/roster/

The main problem is when adding a new entry, they get thrown at the bottom of the table. Even if I order by a certain category that's a numeric datatype (for instance, volume 1), new entries will appear at the bottom of that category and not be integrated in a cohesive order.

If I could order by volume, ep, and page for each query, then it would all be fine.

laserlight

If I could order by volume, ep, and page for each query, then it would all be fine.

hmm... why cant you order by volume, ep, and page etc?

walterbennet

laserlight wrote:
hmm... why cant you order by volume, ep, and page etc?

Well, I can, but the page part of the order isn't numeric data, and sorts it incorrectly (as in the example: 1, 121, 21, 31). I'll make a quick example so you can see it for yourself.

http://www.skullknight.net/php/newtest4.html

Sort by "everything" and "humans" in "traditional" and check out the page sequencing. Weird, eh?

Here's my query, btw:

"SELECT * FROM roster_test WHERE classs IN ($class_types) && species IN ($species_types) ORDER by $searchtype, page";

Thanks for taking the time to help.

walterbennet

Nevermind. I just realized that even this won't solve my problem 100%. If sorted Chronologically, the page sequence throws everything off. I have to find another way to sort the table after adding a new entry... argh.

I should get some sleep... I probably could have circumvented making this thread if I could think straight. Sorry to have wasted some of your time. :bemused: Thanks anyway though.