By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,739 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,739 IT Pros & Developers. It's quick & easy.

PHP and PostgreSQL question on identifier limits.

P: n/a
Hello,

I am migrating from MySQL to PostgreSQL and ran into a limitation of
PostgreSQL. I ran into this because of a technique I use for displaying
results of a SELECT query. In some cases I like to have the heading of some
columns in the table be clickable, usually to sort output by that column.

I have common a function I use that displays the results of a query in an HTML
table with the column headings being the field names. Here is a snippet of
code showing a typical query:

================================================== =====
$_GET['sort'] && $_SESSION['sort']=$_GET['sort'];
$sort=$_SESSION['sort'];
$sort || $sort="field1";

$query="SELECT
field1 AS \"<a href=$PHP_SELF?sort=field1>field1</a>\",
field2 AS \"<a href=$PHP_SELF?sort=field2>field2</a>\",
field3 AS \"<a href=$PHP_SELF?sort=userid>field3</a>\"
FROM my_table ORDER BY $sort";

$tbl=new Table($query);
$tbl->print();
================================================== =====

The problem I ran into is when the "AS" exceeds 63 characters --- which it
does NOT in the above example, but sometimes DOES in more complex real-life
queries. In those cases I get a PostgreSQL error saying that the identifier
is too long. Looking at the PostgreSQL documentation I see this is set to 63
characters by default but can be changed with a recompile.

Before I recompile PostgreSQL I would like to re-think my strategy for making
column headings clickable links.

Does anyone else create HTML tables with clickable headers? If so, do you use
a similar or different technique?

--
Robert C. Paulsen, Jr.
ro****@paulsenonline.net

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi,
$_GET['sort'] && $_SESSION['sort']=$_GET['sort'];
$sort=$_SESSION['sort'];
$sort || $sort="field1";
First of all I don't feel very comfortable with this exotic syntax, but
this is not the right place to discuss about it ;)

$query="SELECT
field1 AS \"<a href=$PHP_SELF?sort=field1>field1</a>\",
field2 AS \"<a href=$PHP_SELF?sort=field2>field2</a>\",
field3 AS \"<a href=$PHP_SELF?sort=userid>field3</a>\"
FROM my_table ORDER BY $sort";

$tbl=new Table($query);
$tbl->print();


Is there a good reason why you don't add hyperlinks inside your Table
class, instead than using column names for such a purpose?
P.S.
Handling column sorting like you're doing is a big security risk! I hope
you are doing some more checks on $sort, otherwise SQL injection attacks
would be very easy to do.
Best regards
--
Matteo Beccati
http://phpadsnew.com/
http://phppgads.com/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

P: n/a
On Sunday 15 August 2004 06:58 pm, Matteo Beccati wrote:
Hi,
$_GET['sort'] && $_SESSION['sort']=$_GET['sort'];
$sort=$_SESSION['sort'];
$sort || $sort="field1";
First of all I don't feel very comfortable with this exotic syntax, but
this is not the right place to discuss about it ;)


I guess "exotic" depends on your background.
$query="SELECT
field1 AS \"<a href=$PHP_SELF?sort=field1>field1</a>\",
field2 AS \"<a href=$PHP_SELF?sort=field2>field2</a>\",
field3 AS \"<a href=$PHP_SELF?sort=userid>field3</a>\"
FROM my_table ORDER BY $sort";

$tbl=new Table($query);
$tbl->print();


Is there a good reason why you don't add hyperlinks inside your Table
class, instead than using column names for such a purpose?


That might be the best solution. This code has evolved from a time before I
was using classes. Used to be a simple function call. With classes I could
add the ability to assign links to the headers.


P.S.
Handling column sorting like you're doing is a big security risk! I hope
you are doing some more checks on $sort, otherwise SQL injection attacks
would be very easy to do.


Good point. Using your suggestion about implementing this inside the Table
class will make this easier to control.

--
Robert C. Paulsen, Jr.
ro****@paulsenonline.net

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.