Re: MySQL ORDER BY or PHP Sort? Oops.

[René Fournier <rene.fournier@xxxxxxxxxxx>]

From: René Fournier <rene.fournier@xxxxxxxxxxx>
Date: Mon Dec 03, 2001  06:11:23  PM US/Mountain
To: Benjamin Pflugmann <benjamin-mysql@xxxxxxxxxxxx>
Cc: "php-general@xxxxxxxxxxxxx" <mysql@xxxxxxxxxxxxxxx>
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, siim_e@xxxxxxxxxxx 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
(Baby>Genesis>Super>Predator>Millennium)...

------

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!

...Rene
[...]

--
benjamin-mysql@xxxxxxxxxxxx

---------------------------------------------------------------------
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 <mysql-thread92949@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-
rene.fournier=markada.com@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



---
René Fournier
renefournier@xxxxxxxxx


---------------------------------------------------------------------
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 <mysql-thread92951@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php