Re: [SQL] placeholders

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