Re: [PHP] Reordering results for a report

That's how we did it in PHP & Postgres.

Now we use the Tigra suite of javascript functions from SoftComplex.com
that lets the user order the data however they want. Transfers the load
from our servers to the client browser which suits me just fine.

BTW, I have no affliation with SoftComplex and have only recently
started using their javascript but if a) your dataset is relatively
small(1), b) your budget is also small(2) c) you hate having to write
more code than you need too they're worth a look.

Greg

(1) Small dataset being about 20,000 items for their tree javascript
depending on the tree structure and about 5,000 rows for the
table javascript

(2) $119 at SoftComplex gets you the tree & table javascripts plus a
tooltip, drop down menu, calculator and a calendar functions. All
work pretty good when compared to the price.

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 7: don't forget to increase your free space map settings

--
Greg Spiegelberg
Sr. Product Development Engineer
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: gspiegelberg(at)Cranel(dot)com
Cranel. Technology. Integrity. Focus.