Reordering results for a report

[Nathaniel Price]

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.

--
___________________________
Nathaniel Price
http://www.tesserportal.net
Webmaster

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[scott.marlowe]

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

[Bruno Wolff III]

On Fri, Dec 12, 2003 at 10:37:27 -0800,
Nathaniel Price <np****@tesseract.net> wrote:

So is there a way to make a query where I can sort arbitrarily without
having to create additional data in the database?

You have to save the state somewhere. If there is somewhere else other
than the database you can do this (e.g. cookies, URLs, etc...) then
you might be able to do it.

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

[Nathaniel Price]

On 12/12/2003 1:16 PM, Bruno Wolff III wrote :

On Fri, Dec 12, 2003 at 10:37:27 -0800,
Nathaniel Price <np****@tesseract.net> wrote:

So is there a way to make a query where I can sort arbitrarily without
having to create additional data in the database?

You have to save the state somewhere. If there is somewhere else other
than the database you can do this (e.g. cookies, URLs, etc...) then
you might be able to do it.

Drat. I was hoping that you could use some sort of subquery... ah well.

However, I was just looking at the documentation for CREATE TABLE and
noticed that you can create temporary tables which only last as long as
the "session". I think I'll probably use these to store the sorting
order by creating tables with a unique name (perhaps something generated
by PHP's uniqid()) and joining on that-- not exactly what I had in mind,
but at least that way I don't have to worry so much about garbage
collection. Although I'm not sure how they'd work with persistant
database connections in PHP...

--
___________________________
Nathaniel Price
http://www.tesserportal.net
Webmaster

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Greg Stark]

Nathaniel Price <np****@tesseract.net> writes:

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

You could do something like

select id,offer
from tab
order by case when id = 3 then 1 when id = 1 then 2 when id = 2 then 3 end

But a) if there are thousands of records you're going to have rather a huge
query and b) where are you going to store this ordering?

--
greg
---------------------------(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