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!
Thanks everyone!
...Rene
On Monday, December 3, 2001, at 06:49 PM, Martin Towell wrote:
can you have another field in your table(s) for a weighting and then
sort
on that?
otherwise you'll have to use php
-Original Message-
From: René Fournier [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 04, 2001 12:16 PM
To: Benjamin Pflugmann; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [PHP] Re: MySQL ORDER BY or PHP Sort? Oops.
From: René Fournier [EMAIL PROTECTED]
Date: Mon Dec 03, 2001 06:11:23 PM US/Mountain
To: Benjamin Pflugmann [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
Subject: Re: MySQL ORDER BY or PHP Sort? Oops.
Here is the snippet of code that is presenting a challenge:
---
?php
// FETCH MODELS TECH SPECS
function models ($lang,$db) {
$modelsheader = mysql_fetch_array(mysql_query(SELECT * FROM models
WHERE lang='$lang' AND key1='header',$db));
$result = mysql_query(SELECT * FROM models WHERE key1='data' ORDER
BY price ASC,$db);
mysql_close();
include ('../common/models.inc');
}
models($lang,$db);
// CREATE A MULTI-DIMENSIONAL MODELS ARRAYS BASED ON THE NUMBER OF MODEL
ROWS IN THE TABLE
$i = 0;
do {
$allmodels[$i] = $models;
$i++;
} while ($models = mysql_fetch_array($result));
// NUMBER OF MODELS
$first = 1;
$last = sizeof($allmodels)-1;
$colspan = ($last * 2)+1;
?
---
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.
Many thanks for all the help, fellas.
...Rene
On Monday, December 3, 2001, at 05:31 PM, Benjamin Pflugmann wrote:
Hi.
Because you said, Rene, that you already know how to sort according to
one of both criteria, I presume ordering the series column is not the
problem (e.g. because it is an enum or something alike).
Then the solution would simply be to do something like
SELECT * FROM my_table WHERE ... ORDER BY series, price
I.e. simply list the order criteria which work seperately in the ORDER
BY clause. If this is not appliable, please elaborate.
Btw, it is *much* easier to help if you had quoted a (partial) working
query (which include table and column names and so on). Also, how the
series are stored would have been of interest, because this will
influence how the ordering is specified.
Also, I do not understand why you put emphasis on the fact that the
series is sorted in an unusual way if you later say that you already
know how to order by it?! Is this relevant to the problem at all?
Bye,
Benjamin.
On Tue, Dec 04, 2001 at 12:36:34AM +0200, [EMAIL PROTECTED] wrote:
Well I think mysql doesnt allow you to do this that simple as mysql
doesnt know how you want to sort it exactly. So my suggestion would be
to
give a value to each series, eg. Baby = 1,
Genesis=2,Super=3,Predator=4,Millennium=5 and then order by series.
Cheers
Siim Einfeldt
One more thing, very important: I want to specify the Series sort order,
not alphabetically, but by a non-obvious way
(BabyGenesisSuperPredatorMillennium)...
--
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.)
Thanks
