>I've been creating a site for someone using MySQL and PHP4.
>Basically the table concerned is structured like this;
>
>id int(5) UNSIGNED No auto_increment Primary
>ship varchar(50) No Index
>year varchar(15) No Index
Sir, change the type of this column to Date, which stores the date as
yyyy-mm-dd. This will allow you to order the records by the date. You
can display the date in a different format using Date_format(). Using
a 15 character varchar field for data that is exclusively dates and
requires a maximum of 10 characters is an invitation for trouble.
Also, since the column contains the full date of the voyage, change
the name of the column from year (it's not the year, it's the date)
to something like sail_date or embarked.
>voyage varchar(50) Yes
>sex varchar(50) Yes
How do you determine the sex of a voyage? And why does it take 50
characters to specify it? :-)
>notes text Yes
>
>Everthing is working fine except the ship order in which the pages are
>generated.
>
>For example, the following are ships names and yes the records do show the
>voyage date (they are in official records that way and I can change them. I
>prudently added the year field and the year is put in that field as well.
>Some ships don't have this date after their name, sometimes they have just a
>voyage number.
>
>Henrietta
>Henry
>Henry Porcher
>Hibernia
>Hindostan (1)
>Hindostan (2)
>Hyderabad (1)
>Hyderabad (2)
>Hyderabad (3)
>Marian Watson (08-06-1842)
>Marian Watson (28-10-1841)
>Marion Watson (08-03-1842)
>
>Here in lies the problem, these records have been entered at different times
>so their id's are all over the place
>
>When I get these records and display them, they will be in the correct
>alphanumeric order except for the ones with the date after them. they will
>only display in the order they were entered.
>
>I've tried the following SELECTs
>
>$result = mysql_query("SELECT * FROM ships ORDER BY ship",$db)
>$result = mysql_query("SELECT * FROM ships ORDER BY ship, year",$db)
>
>but they dont sort the way we want them to (in year order) as per below
>
>Henrietta
>Henry
>Henry Porcher
>Hibernia
>Hindostan (1)
>Hindostan (2)
>Hyderabad (1)
>Hyderabad (2)
>Hyderabad (3)
>Marian Watson (28-10-1841)
>Marion Watson (08-03-1842)
>Marian Watson (08-06-1842)
>
>Anyone got any ideas?
>
>Thanks in advance
>
>Howard Picken
>[EMAIL PROTECTED]
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
