Problems compiling 7.1 with TCL support on Solaris 7

Hi, I want to try and optimize my calls to postgreSQL from PHP. If
I only need one field from a select statement, what should I use? I am
currently using:
$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id)
FROM article)");
$maxid = pg_fetch_array($q, 0);
echo "The highest id is ". $maxid[0];
What can I use besides an array to get a single value? In general, using
a single variable always saves more memory than using an array? Thank
you.
Jason

On Thu, 3 May 2001, Jason wrote:

Hi, I want to try and optimize my calls to postgreSQL from PHP. If
I only need one field from a select statement, what should I use? I am
currently using:
$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id)
FROM article)");
$maxid = pg_fetch_array($q, 0);
echo "The highest id is ". $maxid[0];

Your select statement is the same thing as simply

SELECT max(id) FROM article

There is no reason that I can see to use the subselect unless you're
interested in multiple matching rows or other columns (which it appears
that you are not).

What can I use besides an array to get a single value? In general, using
a single variable always saves more memory than using an array?

Don't worry about saving memory. All your variables are dynamically
allocated and cleaned up automatically when no longer needed.

To fetch a single value from a select statement, I use a function like
this one:

function pg_fetch ($conn, $sql) {
$q = pg_exec($conn, $sql);
if (pg_numrows($q) > 0) {
return pg_result($q, 0, 0);
} else {
return "";
}
}

You can declare the function on a separate file if you'll use it from
multiple php scripts and then use require_once("functions.php") from each
script that uses it.

Calling it is as easy as:

$maxid = pg_fetch($conn, "SELECT max(id) FROM article");

Hope this helps.
--
Tod McQuillin

Jason,

Look into the pg_result() function. You would use it something like
this:

$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id) FROM
article)");
$maxid = pg_result($q,0,0); # pg_result($result,$row,$column_num)
echo "The highest id is ". $maxid[0];

Hope this helps.

Chris Ryan
chris@greatbridge.com

Jason wrote:

First off, cross-posting is rude. Don't post to more than one list at a
time. Post your question to the most relevant list first and if you don't
get help, then try another one.

As for your select statement, why do you have the select around the other
select? Why don't you just have this:

SELECT MAX(id) FROM article

As for how the result is returned, not much you can do about it. That is
the way PHP is. As a matter of fact, that is the way a vast majority of
database interaction programs are. They send an array so that the field
name can accompany it.

Only optimization you can do is fix the select statement.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Jason" <gee308@mediaone.net>
To: <pgsql-php@postgresql.org>
Cc: <pgsql-novice@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Thursday, May 03, 2001 8:06 AM
Subject: [PHP] psql with PHP question

Andy Howarth <andy@is3-design.com> writes:

However when I try to add TCL support, the following happens during
'make':

make[3]: Entering directory
`/export/home/andy/postgresql-7.1/src/pl/tcl'
cc -O -KPIC -I../../../src/include -DHAVE_UNISTD_H=1 -DHAVE_LIMITS_H=1
-DHAVE_GETCWD=1 -DHAVE_OPENDIR=1 -DHAVE_STRSTR=1 -DHAVE_STRTOL=1
-DHAVE_TMPNAM=1 -DHAVE_WAITPID=1 -DHAVE_UNISTD_H=1 -DHAVE_SYS_PARAM_H=1
-DUSE_TERMIOS=1 -DHAVE_SYS_TIME_H=1 -DTIME_WITH_SYS_TIME=1
-DHAVE_TZNAME=1 -DHAVE_TIMEZONE_VAR=1 -DHAVE_ST_BLKSIZE=1
-DSTDC_HEADERS=1 -DNO_UNION_WAIT=1 -DNEED_MATHERR=1 -DRETSIGTYPE=void
-DHAVE_SIGNED_CHAR=1 -DHAVE_SYS_IOCTL_H=1 -DHAVE_SYS_FILIO_H=1 -c -o
pltcl.o pltcl.c
make[3]: cc: Command not found
make[3]: *** [pltcl.o] Error 127

You have a broken Tcl installation.

pltcl tries to use the compiler and switches specified in Tcl's
tclConfig.sh file. Evidently the copy of that file that you have
has nothing to do with your system.

I suggest reinstalling Tcl from source, to make sure that its recorded
build configuration has something to do with reality...

regards, tom lane

Thanks, I'll use that function. I also didn't realize the second select
statement was unnecessary.
Jason
Tod McQuillin wrote:

Thanks for the help, sorry for cross posting.
I have 2 more questions. I am a newb with psql and PHP. I am creating a
slashdot like site. First, what is the best way to store an article? Inside
psql or just reference the path inside psql or is there another method? I am
currently using the path inside psql then use readfile() from PHP to display
the file. Second, Can you guys with experience tell me where I am writing
code improperly or give some tips? This short code segment just displays the
article and comments posted with it. Thanks:

<?
include("./phpinc/psql.php");
include("./phpinc/func.php");
include("./phpinc/var.php");
include("/usr/local/apache/phpinc/variables");
include("/usr/local/apache/phpinc/header");

if (!$conn)
echo "Site is temporarily down";

else {
if(isset($sid)){
$q1 = pg_Exec($conn,"select author,title,path,description from article
where sid=$sid");
$arr = pg_fetch_array($q1, 0);
echo "By ". $arr["author"] .": ". $arr["title"] ."<br>";
echo $arr["description"] ."<br><br><br>";
echo $arr["path"]."<br>"; //this line is just for seign if the path is set
correctly
readfile($arr["path"]);
// echo $arr["path"];

//next 3 lines are jut for replying to the article
echo "<table width=100% bgcolor=$table1><tr><td align=right><form
action=submit.php?sid=$sid method=post>";
echo "<input type=submit value=Post></tr></td></table>";
echo "<BR><BR><BR><BR>";

//start the comments section here
$q3 = pg_Exec($conn, "select cid from comment where article='$sid'");
$maxid = pg_numrows($q3);
$q2 = pg_Exec($conn,"select uname,time,date,post,ctitle,cid from comment
where
article=$sid");
if(!empty($maxid)){
for($i=0;$i<$maxid;$i++){
$arr2 = pg_fetch_array($q2, $i);
echo "By ". $arr2["uname"] .": ". $arr2["ctitle"] ."<br>";
echo $arr2["post"] ."<br>";
echo "<a href=./submit.php?sid=$sid&cid=". $arr2["cid"] .">Reply to
this</a><br><br>";
}
}
}
}
include("/usr/local/apache/phpinc/footer");

?>

Rasputin wrote:

If fixing tcl is too scary (no offence) just become root and type

ln -s `which gcc` /bin/cc

Tried that, gcc spits out the -KPIC option and then ld joins in the fun
by complaining loudly. (See my original post).

Tom Lane wrote:

You have a broken Tcl installation.

pltcl tries to use the compiler and switches specified in Tcl's
tclConfig.sh file. Evidently the copy of that file that you have
has nothing to do with your system.

Thanks Tom. I asked the colleague who installed it, he obtained and
installed a precompiled binary...

Now all I have to do is get Tk to install properly, never had much luck
with that on Solaris. (Missing X files.)

Here goes. Thanks for the replies.

Regards,
Andy.

If they are just text files, run the most current version of Postgres and
store the text in the database. The TOAST support for text is supposed to
be excellent.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Jason" <gee308@mediaone.net>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: <pgsql-php@postgresql.org>
Sent: Thursday, May 03, 2001 10:06 AM
Subject: 2 more questions(Was: Re: [PHP] psql with PHP question)

Thanks for the help, sorry for cross posting.
I have 2 more questions. I am a newb with psql and PHP. I am creating a
slashdot like site. First, what is the best way to store an article?

Inside

psql or just reference the path inside psql or is there another method? I

am

currently using the path inside psql then use readfile() from PHP to

display

the file. Second, Can you guys with experience tell me where I am writing
code improperly or give some tips? This short code segment just displays

the

article and comments posted with it. Thanks:

<?
include("./phpinc/psql.php");
include("./phpinc/func.php");
include("./phpinc/var.php");
include("/usr/local/apache/phpinc/variables");
include("/usr/local/apache/phpinc/header");

if (!$conn)
echo "Site is temporarily down";

else {
if(isset($sid)){
$q1 = pg_Exec($conn,"select author,title,path,description from article
where sid=$sid");
$arr = pg_fetch_array($q1, 0);
echo "By ". $arr["author"] .": ". $arr["title"] ."<br>";
echo $arr["description"] ."<br><br><br>";
echo $arr["path"]."<br>"; //this line is just for seign if the path is

set

correctly
readfile($arr["path"]);
// echo $arr["path"];

//next 3 lines are jut for replying to the article
echo "<table width=100% bgcolor=$table1><tr><td align=right><form
action=submit.php?sid=$sid method=post>";
echo "<input type=submit value=Post></tr></td></table>";
echo "<BR><BR><BR><BR>";

//start the comments section here
$q3 = pg_Exec($conn, "select cid from comment where article='$sid'");
$maxid = pg_numrows($q3);
$q2 = pg_Exec($conn,"select uname,time,date,post,ctitle,cid from comment
where
article=$sid");
if(!empty($maxid)){
for($i=0;$i<$maxid;$i++){
$arr2 = pg_fetch_array($q2, $i);
echo "By ". $arr2["uname"] .": ". $arr2["ctitle"] ."<br>";
echo $arr2["post"] ."<br>";
echo "<a href=./submit.php?sid=$sid&cid=". $arr2["cid"] .">Reply

to

Also... is there any reason you don't just do:

SELECT MAX(id) FROM article

rather than what you have below? It would get you the same thing wouldn't
it and save a query...

On Thu, 3 May 2001, Chris Ryan wrote:

In my opinion, these queries make the same :
SELECT id FROM article WHERE id=(SELECT MAX(id) FROM article);
SELECT MAX(id) FROM article;

but the latter one is much more simple and you do not overload your pg.
(by the way, I think id = (SELECT ...) isn't handled gracefully.)

----- Original Message -----
From: "Jason" <gee308@mediaone.net>
To: <pgsql-php@postgresql.org>
Cc: <pgsql-novice@postgresql.org>; <pgsql-general@postgresql.org>
Sent: 2001. m�jus 3. 14:06
Subject: [PHP] psql with PHP question

Philip Hallstrom wrote:

Also... is there any reason you don't just do:

SELECT MAX(id) FROM article

rather than what you have below? It would get you the same thing wouldn't
it and save a query...

On Thu, 3 May 2001, Chris Ryan wrote:

Jason,

Look into the pg_result() function. You would use it something like
this:

$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id) FROM
article)");
$maxid = pg_result($q,0,0); # pg_result($result,$row,$column_num)
echo "The highest id is ". $maxid[0];

Hope this helps.

Chris Ryan
chris@greatbridge.com

Jason wrote:

Hi, I want to try and optimize my calls to postgreSQL from PHP. If
I only need one field from a select statement, what should I use? I am
currently using:
$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id)
FROM article)");

Or even more efficiently:

SELECT id FROM article ORDER BY id DESC LIMIT 1;

Since that will use an index on article.id if article is a big table, has been
vacuum analyzed, and has an index available.

Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709

On Thu, 3 May 2001, Gyozo Papp wrote:

In my opinion, these queries make the same :
SELECT id FROM article WHERE id=(SELECT MAX(id) FROM article);
SELECT MAX(id) FROM article;

but the latter one is much more simple and you do not overload your pg.
(by the way, I think id = (SELECT ...) isn't handled gracefully.)

Yep, it's much better.

If you want to get more than just the ID, try:

select * from article order by id desc limit 1;

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Thu, 3 May 2001, Gyozo Papp wrote:

In my opinion, these queries make the same :
SELECT id FROM article WHERE id=(SELECT MAX(id) FROM article);
SELECT MAX(id) FROM article;

but the latter one is much more simple and you do not overload your pg.
(by the way, I think id = (SELECT ...) isn't handled gracefully.)

Hi, although I agreee with the first part: the subselect is redundat in
this case, I don't understand de second one. The subselect will return one
value, or am I wrong? So, I think the operator = will be correct, or not?

Saludos, jesus.

Hello,

just some more word :

In my opinion, these queries make the same :
SELECT id FROM article WHERE id=(SELECT MAX(id) FROM article);
SELECT MAX(id) FROM article;

but the latter one is much more simple and you do not overload your pg.
(by the way, I think id = (SELECT ...) isn't handled gracefully.)

Hi, although I agreee with the first part: the subselect is redundat in
this case, I don't understand de second one. The subselect will return one
value, or am I wrong? So, I think the operator = will be correct, or not?

I mean the second sentence that I don't think the planner/optimizer is so clever to reduce the former query to the latter one.
You're right the sublselect will return one value, but as far as I know this value should be interpreted as a set of one value (because of the select).