I have a list of subjects, such as "Linux", "Open Source", and "the
World Wide Web". The subjects are stored in MySQL and being retrieved
via php. I currently organize them alphabetically with MySQL's "ORDER
BY ASC" argument, however, if there is a preceding "the " or "a " then
that is considered as part of the alphabetical order. Thus, all the
subjects starting with "the " are grouped together, as are the
subjects starting with "a ". How can I order by ascending, without
taking the preceding "the " or "a " into account?

** Example:
Now, the list is ordeded like this:
a Distribution
a Text Editor
a Virus
Bluetooth
Copyleft
DRM
Fedora
Firefox

However, I'd like it to be ordered like this:
Bluetooth
Copyleft
a Distribution
DRM
Fedora
Firefox
a Text Editor
a Virus

Current code:
$query = "SELECT subject FROM table ORDER BY subject asc";
$result = mysql_query($query);


Thanks in advance.

Dotan Cohen

http://what-is-what.com/what_is/world_wide_web.html

On 2006-11-06 Dotan Cohen wrote:
> I have a list of subjects, such as "Linux", "Open Source", and "the
> World Wide Web". The subjects are stored in MySQL and being retrieved
> via php. I currently organize them alphabetically with MySQL's "ORDER
> BY ASC" argument, however, if there is a preceding "the " or "a " then
> that is considered as part of the alphabetical order. Thus, all the
> subjects starting with "the " are grouped together, as are the
> subjects starting with "a ". How can I order by ascending, without
> taking the preceding "the " or "a " into account?

Make a second column that only contains
ALTER TABLE table ADD cooked_subject;
UPDATE table SET cooked_subject = ereg_replace('^(a|the) ', '', subject);
(I don't know how the regular expression function was called exactly but you get the idea)

SELECT subject FROM table ORDER BY cooked_subject;

Of course you could also put the regular expression in the SELECT but that would be slower.



bye,

-christian-

On 06/11/06, Christian Hammers <ch@lathspell.de> wrote:
> On 2006-11-06 Dotan Cohen wrote:
> > I have a list of subjects, such as "Linux", "Open Source", and "the
> > World Wide Web". The subjects are stored in MySQL and being retrieved
> > via php. I currently organize them alphabetically with MySQL's "ORDER
> > BY ASC" argument, however, if there is a preceding "the " or "a " then
> > that is considered as part of the alphabetical order. Thus, all the
> > subjects starting with "the " are grouped together, as are the
> > subjects starting with "a ". How can I order by ascending, without
> > taking the preceding "the " or "a " into account?
>
> Make a second column that only contains
> ALTER TABLE table ADD cooked_subject;
> UPDATE table SET cooked_subject = ereg_replace('^(a|the) ', '', subject);
> (I don't know how the regular expression function was called exactly but you get the idea)
>
> SELECT subject FROM table ORDER BY cooked_subject;
>
> Of course you could also put the regular expression in the SELECT but that would be slower.

Would it really slow it down that much? I'll consider the
cooked_subject idea then, if noone has any other suggestions.

Thanks.

Dotan Cohen

http://www.lyricslist.com/lyrics/art...64/beatles.php
http://gmail-com.com/

On 2006-11-06 Dotan Cohen wrote:
> > Make a second column that only contains
> > ALTER TABLE table ADD cooked_subject;
> > UPDATE table SET cooked_subject = ereg_replace('^(a|the) ', '', subject);
> > (I don't know how the regular expression function was called exactly but you get the idea)
> >
> > SELECT subject FROM table ORDER BY cooked_subject;
> >
> > Of course you could also put the regular expression in the SELECT but that would be slower.
>
> Would it really slow it down that much?

"slow" always depends.. if the table has only 200 rows and the query is run
twice a day, don't care about speed

-christian-