René,
> I want to select about 25 rows from a table, and sort them by two
> criteria. First, by each row's Series field ("Baby", "Genesis", "Super",
> "Predator", "Millennium" are the various Series, and the order I'd like
> the rows in the array). Within each Series, I'd like the rows sorted by
> their Price field, ascending. For example:
> Baby $5
> Baby $10
> Baby $15
> Genesis $20
> Genesis $35
> Genesis $50
> ...and so on.
> Now, I know how to structure my MySQL Select statment such that the rows
> it pulls from the table will be either sorted by Price OR by Series, but
> not both, in the way I'd like. Does anyone know if it's possibly to do
> this in the Select statement itself? (I'd rather do it that way, than
> resort in PHP.)
> The problem is, if I sort by the price field, the ordering of the series
> gets mixed up (since low-end models in higher-end series cost more than
> some higher-end models in lower-end series (confused?? I am :-) If I
> just sort by series, I don't get the prices in order within each series
> (big problem). And if I sort by BOTH series and price, which is the
> logical thing to do, and which many people have kindly suggested (and
> which I have tried), well, it doesn't work, because the series must be
> sorted in a non-alphabetical way.
>
> I've looked at PHP's myriad sorting functions, and well--maybe I need
> more sun (I live in Canada... winter...)--I can't see a 'simple' way to
> do a non-numerical, non-alphabetical (user-defined?) sort on the Series
> fields, then subsort each series by price.
> can you have another field in your table(s) for a "weighting" and then
> sort on that?
> otherwise you'll have to use php
> You know, in spite of all the good advice I've received, I think that's
> what I'll have to do. I say "have to" because inserting another field
> into the table creates some complications (long story... related to the
> web admin tool I made for editing the tables). Actually, come to think
> of it, there is an existing field that I could reuse for this purpose.
> Yes, that will work!
=Did you manage to solve this? There is a way to accomplish what you want, without
this resorting to an extra
field or PHP (providing the range of values in the Series field is manageable).
SELECT
SERIES
, PRICE
, FIELD( SERIES, "Baby", "Genesis", "Super", "Predator", "Millennium" ) as PRIORITY
FROM TblNm
ORDER BY PRIORITY, PRICE
NB not having access to your db I haven't properly tested this code, nor was I quite
clear from your
example/text about the relative importance of PRIORITY and PRICE in the sequencing, so
please experiment/test.
Regards,
=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
