Re: Reordering results for a report

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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




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




[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux