Received: from axe.net.au (root@axe.net.au [203.35.182.2]) by hub.org (8.9.1/8.9.1) with ESMTP id SAA24525 for ; Wed, 6 Jan 1999 18:55:33 -0500 (EST) (envelope-from martin@axe.net.au) From: martin@axe.net.au Received: from hatchet (martin@mattock.axe.net.au [203.35.182.4]) by axe.net.au (8.9.2/8.9.2) with SMTP id KAA12338; Thu, 7 Jan 1999 10:55:10 +1100 (EST) Message-Id: <199901062355.KAA12338@axe.net.au> X-Sender: park2@axe.net.au X-Mailer: QUALCOMM Windows Eudora Pro Version 4.0 Date: Thu, 07 Jan 1999 10:52:34 +1100 To: "Gene Selkov Jr." , pgsql-sql@postgreSQL.org Subject: Re: [SQL] placeholders In-Reply-To: <199901061604.KAA02423@antares.mcs.anl.gov> References: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" At 03:05 7/01/99 , you wrote: >> Hi, >> >> I am using >> - Postrges 6.3.2 >> - DBI 0.93 >> - DBD-pg 0.73 >> - Slackware 3.3 Linux >> >> I am accessing the database using a perl >> CGI program. >> >> I need to be able to list records from a >> database in various sort orders. The user >> needs to be able to select the sort order >> using a web form. > >[...] > >> >> I want to be able to change the code so the >> field name in the order by clause is variable >> >> eg. instead of >> ORDER BY title DESC >> >> I want to say >> ORDER BY :4 DESC >> >> and in the execute statement >> $sth1->execute (("$category\%"),("$keywords\%"), ("$postedby\%")) >> >> add a fourth variable ("$orderby") which >> will be set to title, dateposted or >> userlastupdate >> >> However if I use ("$orderby") as the >> fourth variable and set $orderby to >> title then my trace shows DBI puts >> 'title' into the SQL SELECT statement >> not title and I get an invalid syntax >> error. I need to find a way to stop >> putting the ' ' around title. >> >> Is there any way I can make the order by >> operand a variable ?? >> > > What's wrong with: > > @column = ("catalogid", "productid", ..., "category"); > > ... ORDER BY $column[3] DESC ... Gene, Thanks for your reply. I don't think I can put a perl variable $column[3] into an sql prepare statement as it gets passed straight through as $column[3] to DBI and I get a syntax error from DBI. ERROR: parser: parse error at or near "column" However the sort in Perl looks like the answer so I will try that next. Regards, Martin > >If you want it real smart, you can run a query to obtain column names >from the database. > >Also, since you are using perl anyway, you might as well delegate >ordering to perl. If you read your entire query output to an array of >strings where values are delimited with something, e. g., "\t", you >could say: > > $colToSortOn = 2; > $descending = 1; # otherwise $descending = undef; > foreach ( sort { > @a = split "\t", $a; > @b = split "\t", $b; > ($descending ? $b[$colToSortOn] <=> $a[$colToSortOn] : $a[$colToSortOn] <=> $b[$colToSortOn]) > } @result # @result comes from your query > ) { > @values = split "\t"; > # do your display stuff here > } > >See man perlfunc /sort SUBNAME for more details on sort() >