From: martin@axe.net.au
To: pgsql-sql@postgresql.org
Subject: placeholders
Date: Wed, 06 Jan 1999 18:01:35 +1100
Message-ID: <199901060704.SAA16837@axe.net.au> (raw)
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.
The code I am using which works fine is
$sth1 = $dbh->prepare
(q
{
SELECT DISTINCT
catalogid,
productid,
reference,
title,
dateposted,
dateremove,
userlastupdate,
email,
webaddress,
phone,
details,
category
FROM product
WHERE (( catalogid = 'mp')
AND ( category like :1 )
AND ( details like :2 )
AND ( userlastupdate like :3 ))
ORDER BY title DESC
}
);
$counter = 0;
$sth1->execute (("$category\%"),("$keywords\%"), ("$postedby\%"))
or die "$DBI::errstr\n";
while ( (
$catalogid,
$productid,
$reference,
$title,
$dateposted,
$dateremove,
$userlastupdate,
$email,
$webaddress,
$phone,
$details,
$category
) = $sth1->fetchrow() )
{
$counter = $counter + 1;
After this the CGI writes out HTML
for each row read.
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 ??
Thanks,
Martin Stewart
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: martin@axe.net.au
Subject: Re: placeholders
In-Reply-To: <199901060704.SAA16837@axe.net.au>
References: <199901060704.SAA16837@axe.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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