pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)  latest in thread

Message-ID: <199901060704.SAA16837@axe.net.au>
Permalink:  ../199901060704.SAA16837@axe.net.au/
Also on:    postgresql.org/message-id/199901060704.SAA16837@axe.net.au

 · 

reply

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