pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reordering results for a report
11+ messages / 6 participants
[nested] [flat]

* Reordering results for a report
@ 2003-12-12 18:37  Nathaniel Price <nprice@tesseract.net>
  0 siblings, 3 replies; 11+ messages in thread

From: Nathaniel Price @ 2003-12-12 18:37 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-12 20:38  scott.marlowe <scott.marlowe@ihs.com>
  parent: Nathaniel Price <nprice@tesseract.net>
  2 siblings, 3 replies; 11+ messages in thread

From: scott.marlowe @ 2003-12-12 20:38 UTC (permalink / raw)
  To: Nathaniel Price <nprice@tesseract.net>; +Cc: pgsql-general@postgresql.org; pgsql-php

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-12 21:16  Bruno Wolff III <bruno@wolff.to>
  parent: Nathaniel Price <nprice@tesseract.net>
  2 siblings, 1 reply; 11+ messages in thread

From: Bruno Wolff III @ 2003-12-12 21:16 UTC (permalink / raw)
  To: Nathaniel Price <nprice@tesseract.net>; +Cc: pgsql-general@postgresql.org

On Fri, Dec 12, 2003 at 10:37:27 -0800,
  Nathaniel Price <nprice@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.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PHP] Reordering results for a report
@ 2003-12-12 21:25  Greg Spiegelberg <gspiegelberg@cranel.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 0 replies; 11+ messages in thread

From: Greg Spiegelberg @ 2003-12-12 21:25 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; +Cc: pgsql-php

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@Cranel.com
Cranel. Technology. Integrity. Focus.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-12 22:19  Nathaniel Price <nprice@tesseract.net>
  parent: Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 11+ messages in thread

From: Nathaniel Price @ 2003-12-12 22:19 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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

>On Fri, Dec 12, 2003 at 10:37:27 -0800,
>  Nathaniel Price <nprice@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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-12 22:40  Nathaniel Price <nprice@tesseract.net>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 2 replies; 11+ messages in thread

From: Nathaniel Price @ 2003-12-12 22:40 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-general@postgresql.org; pgsql-php

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-12 23:09  scott.marlowe <scott.marlowe@ihs.com>
  parent: Nathaniel Price <nprice@tesseract.net>
  1 sibling, 0 replies; 11+ messages in thread

From: scott.marlowe @ 2003-12-12 23:09 UTC (permalink / raw)
  To: Nathaniel Price <nprice@tesseract.net>; +Cc: 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
> >
> >
> >  
> >
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-13 23:47  Bruno Wolff III <bruno@wolff.to>
  parent: Nathaniel Price <nprice@tesseract.net>
  1 sibling, 0 replies; 11+ messages in thread

From: Bruno Wolff III @ 2003-12-13 23:47 UTC (permalink / raw)
  To: Nathaniel Price <nprice@tesseract.net>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; pgsql-general@postgresql.org; pgsql-php

On Fri, Dec 12, 2003 at 14:40:34 -0800,
  Nathaniel Price <nprice@tesseract.net> 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.
> ?>

It would be possible to use this array in a query, but this wouldn't
work well for large data sets as you would be sending the whole array
back and forth a lot. Presumably the reason you want to do the sort
in the database is because the dataset is large enough that you don't
want to buffer the data in PHP in the first place.

I think the temp table solution won't be that simple either. You will
need connection pooling and some way to keep requests from the same
web session using the same postgres session.

You may want to use regular tables which are named by session id and
the have some clean up process that removes tables that are no longer
needed.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Reordering results for a report
@ 2003-12-14 02:39  Greg Stark <gsstark@mit.edu>
  parent: Nathaniel Price <nprice@tesseract.net>
  2 siblings, 0 replies; 11+ messages in thread

From: Greg Stark @ 2003-12-14 02:39 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Nathaniel Price <nprice@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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [GENERAL] Reordering results for a report
@ 2003-12-15 09:02  Tino Wildenhain <tino@wildenhain.de>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Tino Wildenhain @ 2003-12-15 09:02 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Nathaniel Price <nprice@tesseract.net>; pgsql-php

Hi Scott,

scott.marlowe schrieb:
[...]
> 
> 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";
> }
> 
[...]

This leads to a nice SQL-injection posibility.
At least it has to made sure that no illegal
data can be transported via $orderby

Regards
Tino




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [GENERAL] Reordering results for a report
@ 2003-12-15 16:25  scott.marlowe <scott.marlowe@ihs.com>
  parent: Tino Wildenhain <tino@wildenhain.de>
  0 siblings, 0 replies; 11+ messages in thread

From: scott.marlowe @ 2003-12-15 16:25 UTC (permalink / raw)
  To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Nathaniel Price <nprice@tesseract.net>; pgsql-php

On Mon, 15 Dec 2003, Tino Wildenhain wrote:

> Hi Scott,
> 
> scott.marlowe schrieb:
> [...]
> > 
> > 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";
> > }
> > 
> [...]
> 
> This leads to a nice SQL-injection posibility.
> At least it has to made sure that no illegal
> data can be transported via $orderby

Actually, the $flds[] array var is internally generated, and you'll 
notice that orderby is NOT a PHP var, but a field name.

Good point though, lots of folks "trust" user input without paying 
attention to injection issues, which is one reason I always setup servers 
with magic quotes on.  :-)




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2003-12-15 16:25 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-12 18:37 Reordering results for a report Nathaniel Price <nprice@tesseract.net>
2003-12-12 20:38 ` scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:25   ` Greg Spiegelberg <gspiegelberg@cranel.com>
2003-12-12 22:40   ` Nathaniel Price <nprice@tesseract.net>
2003-12-12 23:09     ` scott.marlowe <scott.marlowe@ihs.com>
2003-12-13 23:47     ` Bruno Wolff III <bruno@wolff.to>
2003-12-15 09:02   ` Tino Wildenhain <tino@wildenhain.de>
2003-12-15 16:25     ` scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:16 ` Bruno Wolff III <bruno@wolff.to>
2003-12-12 22:19   ` Nathaniel Price <nprice@tesseract.net>
2003-12-14 02:39 ` Greg Stark <gsstark@mit.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox