We have an odd sorting situation.
We have a column in the products table that holds a comma-delimited
list of color names in order of decreasing prominence in the item. I
want to generate a list of items that include a target color, with
those items that have the target color listed first at the top of the
list.
I was hoping there was a way to filter data, with regular expressions
perhaps, before sorting, but I don't see that in MySQL so I'll have to
do it in one of a few hard ways...
Option 1: It would be the equivalent of concatenating the results of
two sorted queries: one query returning items whose color lists start
with the target color, and another returning items whose color lists
include but do not start with the target color. I could do that
easily, but I'm not sure how to effectively combine them in a "more"
scheme. Since the resulting lists would sometimes be hundreds of items
long, I'd want to present it in multiple pages.
Option 2: Since the color names are all alphabetic, it could also be
done by replacing the target color in each color list with "1" for the
sake of sorting. (But not in the actual item data.) I suppose this
could be accomplished by making a color list column for each color and
filling them with modified data. Not certain how to do that, though.
I'd want to do it on the fly as items are added and edited.
Option 3: From what I've figured out from the RTFM, there is also a
way to pass a list from a query to Perl. I suppose I could, once I
learned enough Perl, write my own search algorithm and result
generation code.
Option 4: I could continue to do it the way I am on our current
non-intervchange server: Generate the pages offline with a VB program
and upload them by hand. I'd really prefer to have it all
automagically done, but this would work until then.
Anyway, I'd like to know if anyone has any hints on how to go about
any of those first three options, of if there's maybe something I
hadn't thought of.
Thanks!
JA