Thread: psql with PHP question

psql with PHP question

From
Jason
Date:
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


Re: [NOVICE] psql with PHP question

From
Chris Ryan
Date:
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)");
> $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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl




Re: psql with PHP question

From
Tod McQuillin
Date:
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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html




Re: psql with PHP question

From
"Adam Lang"
Date:
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


> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


2 more questions(Was: Re: psql with PHP question)

From
Jason
Date:
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");

?>


Re: psql with PHP question

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

> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




Re: 2 more questions(Was: Re: psql with PHP question)

From
"Adam Lang"
Date:
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
> this</a><br><br>";
>                               }
>                     }
>              }
>           }
>      include("/usr/local/apache/phpinc/footer");
>
> ?>


Re: psql with PHP question

From
Philip Hallstrom
Date:
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)");
> > $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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
"Gyozo Papp"
Date:
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


> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
Joel Burton
Date:
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: [NOVICE] Re: psql with PHP question

From
Andrew McMillan
Date:
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

Re: psql with PHP question

From
Jesus Aneiros
Date:
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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl




Re: psql with PHP question

From
"Gyozo Papp"
Date:
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
latterone.  
You're right the sublselect will return one value, but as far as I know this value should be interpreted as a set of
onevalue (because of the select). 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
"Gyozo Papp"
Date:
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
latterone.  
You're right the sublselect will return one value, but as far as I know this value should be interpreted as a set of
onevalue (because of the select). 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
Joel Burton
Date:
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
Jesus Aneiros
Date:
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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl




Re: psql with PHP question

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

> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




Re: [NOVICE] psql with PHP question

From
Chris Ryan
Date:
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)");
> $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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl




Re: psql with PHP question

From
Philip Hallstrom
Date:
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)");
> > $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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




Re: psql with PHP question

From
Tod McQuillin
Date:
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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html