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 > (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 > [...] > > -- > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > 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 <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --- René Fournier [EMAIL PROTECTED] > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- 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
