Re: Reordering results for a report - Mailing list pgsql-php

Oh, in that case you're gonna need to use a case switch type setup and
order by that.

select a,
  case when a=3 then 0
       when a=1 then 1
       when a=2 then 2
       else 99999
 end as ob
from table
order by ob;

kinda thing.

On Fri, 12 Dec 2003, Nathaniel Price wrote:

> Sorry, that's not really what I'm looking for. When I said that the sort
> order could be arbitrary, I meant /arbitrary/. As in "no amount of ORDER
> BYs will save me from this one" arbitrary; the records could be in any
> order the user specifies.
>
> The idea is that in PHP I'd be using an array to keep track of the
> custom order that the results should be returned in, using the array
> index to store the sorting order and the values to store the table's
> primary key, like so (note that this isn't the code I'm using, it's just
> an example):
>
> <?php
> $sort[0] = 3; //First record to return (3 is the primary key of the record)
> $sort[1] = 1; //Second record to return
> $sort[2] = 2; //Third record to return
> ... //and so on.
> ?>
>
> What I'm hoping is that somehow I can use that array to make a query
> that will return the records in the order that is specified without
> having to create an extra table in the database just to store the sort
> order that I want to use and joining on it. However, as I mentioned in
> my reply to Bruno Wolff III, I'll probably just use temporary tables to
> do it, unless anyone can show me a more elegant solution...
>
> Thanks anyway for your reply.
>
> On 12/12/2003 12:38 PM, scott.marlowe wrote :
>
> >First, I'm crossposting this to pgsql-php, please remove the pgsql-general
> >header when next someone responds.
> >
> >OK, here's how ya do it.  something like this:
> >
> >First, after you run a select, you can use pg_field_name to iterate over
> >the list of fields you're getting back.  I.e. if your select was something
> >like:
> >
> >select a1/a2 as div, a1+a2 as sum, a1-a2 as diff, a1, a2 from table;
> >
> >you could use this:
> >
> >$count = pg_num_fields($res);
> >if (isset($flds)) unset($flds);
> >for ($i=0;$i<$count;$i++){
> >  $flds[]=pg_field_name($res,$i);
> >}
> >
> >Now, when you're printing out the headers for each row, just make the link
> >have something like:
> >
> >print "<url goes here...>?orderby=".$flds[$i]."moreurlstuffhere???";
> >
> >Then, if the orderby is set when you build your query, just append it:
> >
> >if (isset($orderby)){
> >  $query.= "order by ".$orderby"
> >}
> >
> >Add some directional control:
> >
> >if (isset($dir)){
> >  if ($dir=="down") $query.=" DESC";
> >}
> >
> >There's more you can do, but does that kinda get the idea across?  sorry
> >if it's not real detailed.
> >
> >On Fri, 12 Dec 2003, Nathaniel Price wrote:
> >
> >
> >
> >>I'm new to this list, so I'm not sure if this is the right place to post
> >>this. If not, please direct me to where it would be better to post it.
> >>
> >>Anyway, I'm creating a report generation tool of sorts in PHP for a
> >>database. As part of this report generation tool, I'd like to allow the
> >>user to reorder these results arbitrarily. In other words:
> >>
> >>id | offer
> >>---+------------
> >>1  | Offer 1
> >>2  | Offer 2
> >>3  | Offer 3
> >>
> >>could become
> >>
> >>id | offer
> >>---+------------
> >>3  | Offer 3
> >>1  | Offer 1
> >>2  | Offer 2
> >>
> >>However, I don't see any way of reordering the results arbitrarily,
> >>short of creating a table that maps the id numbers to an arbitrary sort
> >>order, joining them and doing an ORDER BY on the sort order numbers,
> >>like so:
> >>
> >>id | offer     | sort
> >>---+-----------+------
> >>3  | Offer 3   | 1
> >>1  | Offer 1   | 2
> >>2  | Offer 2   | 3
> >>
> >>The problems that I have with this solution are
> >>--The sort order would be unique for anybody who uses the system, in
> >>other words, one user may sort one way, and another user another way,
> >>and perhaps simultaneously. I could fix this by using an additional
> >>session identifier in the sort table, but that leads me to the next
> >>problem...
> >>--I'd have to garbage collect this data everytime I'm finished with it,
> >>and since it's likely to only be used once for the actual report
> >>generation and then discarded, it seems like a waste of effort.
> >>
> >>So is there a way to make a query where I can sort arbitrarily without
> >>having to create additional data in the database?
> >>
> >>Thanks for your attention.
> >>
> >>
> >>
> >>
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 9: the planner will ignore your desire to choose an index scan if your
> >      joining column's datatypes do not match
> >
> >
> >
> >
>
>
>