Bryan, I apologise, did I mistakenly call you Brent earlier!? > Indeed. I have a relatively large (few dozen) number > of tables that are accessed via PHP and a web > interface. The script pulls information from these > tables to generate pull-down menus. It would be nice > if these pull-down menus were in some inherent logical > order--alphabetical, say--as they were when I > initially populated the tables.
=presentation sequence should be logical: no question about it. =initial table population sequence has little to do with 'real life' - if things changed and a load more rows were added to the table, you would still want to achieve the first objective. Which brings us to... > However deleting and adding rows have caused the order > to become jumbled--althought the ID numbers are still > associated with the name fields in alphabetical order, > they are no longer returned in order automatically > with a simple SELECT statement. I had hoped there > would be some simple command I could run to shuffle > the rows back around to inherently be in logical > order, so that I wouldn't have to go through scores of > SQL statements in my scripts adding ORDER BY > statements. Perhaps this is not possible, though. > Perhaps MySQL does not care in what order rows are > stored internally, and an ORDER BY clause is the only > way to impose an outside order on them. This is NOT a MySQL issue, it is rooted in relational theory. In a RDBMS there is no concept of storing data in any particular sequence. Concepts of sequence come into such things as indexing that data, and retrieving the data. BTW this is a common question from people who have 'started' with serial file systems and even certain 'personal' database systems, and are now moving into the SQL/relational world. Let's think about adding data to an existing tbl (as a 'for instance'): it would be fastest if the RDBMS INSERTed the data at the end of the existing file - in file system terminology: open file for write-append, write the row, close the file. If the data was to be inserted somewhere mid-file in order to preserve a 'sequence', what would be involved? Boy do I remember struggling with all this different file formats, and their pros and cons, back at Uni! I mentioned indexing. If the index (let's say a single column for 'purity of thought') is 'small' when compared to the size of the row as a whole, an entire file of indexes/indices is going to be significantly smaller (read: easier to manipulate) than the data-table itself. Consequently when rows are added to indexed tbls, the 'data' can be 'thrown in' (he says most unscientifically) and the indexed data can be inserted (using some suitably smart algorithm) into the correct place in the sequence (of the index file). So data is kept as a 'hodge-podge' (with absolutely no predictable/reliable sequence), but indexes/indices are properly maintained. However, if you are a thinking-reader, when I described INSERTing a para back, you may have thought, 'yes slow storing, but faster retrieval - it's a tradeoff' - and you'd be right (in the trade-off notion)! Trouble is, when you stored the data, yesterday, ready for its retrieval today, did you know the sequence that would be demanded, in advance? Another aspect of relational theory is that the way you build the table, with the access methods/relationships you have in mind today, should not 'fix' things so that you can't react/cope with unanticipated requests/demands tomorrow! In order words, just because you want to pull data out of a table in x-sequence today, doesn't limit you to only that (as some older file systems used to do), if you want to use another sequence, even on another column, tomorrow! That's the good news! The bad news is, yes, you either accept SELECTed data back in some random order; or you add an ORDER BY clause. At least it is not as bad as herding cats! =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
