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

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.
>
>