-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Andy, et al --
...and then Andy Jackman said... % % David, % Firstly, to answer your question I don't know of a MySQL function that % may allow you to sort indirectly by the contents of a field - there are How very odd! It seems so natural to me :-) % many many functions and some of them are very specialised, and if you % ever move to another db all those great little functions may not be % there. However, there is a another, more standard (and IMHO better) way: So I'm starting to gather. % % It's generally not a great idea to store lists of things in a single % field. One reason is that you've allocated n chars for the question % order, but what if a survey suddenly gets twice as many questions? - You % then need to resize your columnns or make them all huge to start with. Yeah. I was using varchar to give myself room, but a million-question survey would still blow that :-) % The other reason is that it creates problems like the one you're having % here. Yeah. % % To avoid lists in a field, you need to create additional tables to store % the lists, but then the problem you have of sorting goes away as if by % magic. *That*'s nice :-) % Here's an example for survey and question (note, I've renamed you % original SURVEY_QUESTION table to QUESTION): % create table SURVEY % ( SID int primary key not null auto_increment, % SNAME varchar(20), % ) ; % create table QUESTION % ( QID int primary key not null auto_increment, % QBODY varchar(255), % ) ; % % create table SURVEY_QUESTION % ( SID int , (you'd probably make these 2 fields the primary key) % QID int , % QUESTION_ORDER int % ) ; OK. We were actually looking at putting ORDER as a field in SURVEY (as renamed), and similarly for the options. Is there any particular reason to do it as a separate table rather than as part of the questions table itself? % % Now you can have the same question in many surveys and the % QUESTION_ORDER field in SURVEY_QUESTION allows you to sort the questions Ahhh... OK; I can see this as a reason for a separate table. We won't be reusing questions, though, even if they're identical; that's already been determined as a design decision. % in any order for that survey. (You just put arbitary numbers in % QUESTION_ORDER to make the order work e.g. 10, 20, 30 - by using % multiples of 10 you can insert a question without renumbering the % order.) Gah. That's what I was hoping to avoid. It's like writing BASIC and having to leave yourself room. Even worse, I have to consider the pathological case, like some guy inserting questions in reverse order. Splitting the difference will run out pretty quickly; even counting by thousands is too small for a 10-q survey done that way! How big is MAXINT again? :-) I know that NOT doing so would then require me to update a bunch of fields after a not-at-the-end insertion, which is what put me onto the idea of storing the order in a single spot in the first place. But it seems so much cleaner :-) The only problem is [efficiently] getting the data back out when I want to display it... % % I hope that helps, It does; thanks! % Andy HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/NjmHGb7uCXufRwARAgVXAJwOqYHM4wK2AAMrVqNJDwgfHlqOOQCeM94o aLu93Cy9zCLTlGhwhy7DjKI= =dv6o -----END PGP SIGNATURE----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
