pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
psql with PHP question
12+ messages / 10 participants
[nested] [flat]

* psql with PHP question
@ 2001-05-03 12:06  Jason <gee308@mediaone.net>
  0 siblings, 4 replies; 12+ messages in thread

From: Jason @ 2001-05-03 12:06 UTC (permalink / raw)
  To: pgsql-php; +Cc: pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: psql with PHP question
@ 2001-05-03 12:49  Tod McQuillin <devin@spamcop.net>
  parent: Jason <gee308@mediaone.net>
  3 siblings, 1 reply; 12+ messages in thread

From: Tod McQuillin @ 2001-05-03 12:49 UTC (permalink / raw)
  To: Jason <gee308@mediaone.net>; +Cc: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [NOVICE] psql with PHP question
@ 2001-05-03 12:54  Chris Ryan <chris@greatbridge.com>
  parent: Jason <gee308@mediaone.net>
  3 siblings, 1 reply; 12+ messages in thread

From: Chris Ryan @ 2001-05-03 12:54 UTC (permalink / raw)
  To: Jason <gee308@mediaone.net>; +Cc: pgsql-php; pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: psql with PHP question
@ 2001-05-03 13:05  Adam Lang <aalang@rutgersinsurance.com>
  parent: Jason <gee308@mediaone.net>
  3 siblings, 0 replies; 12+ messages in thread

From: Adam Lang @ 2001-05-03 13:05 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: psql with PHP question
@ 2001-05-03 13:43  Jason <gee308@mediaone.net>
  parent: Tod McQuillin <devin@spamcop.net>
  0 siblings, 0 replies; 12+ messages in thread

From: Jason @ 2001-05-03 13:43 UTC (permalink / raw)
  To: Tod McQuillin <devin@spamcop.net>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re:  psql with PHP question
@ 2001-05-03 15:29  Philip Hallstrom <philip@adhesivemedia.com>
  parent: Chris Ryan <chris@greatbridge.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Philip Hallstrom @ 2001-05-03 15:29 UTC (permalink / raw)
  To: Chris Ryan <chris@greatbridge.com>; +Cc: Jason <gee308@mediaone.net>; pgsql-php; pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [PHP] psql with PHP question
@ 2001-05-03 16:19  Gyozo Papp <pgerzson@freestart.hu>
  parent: Jason <gee308@mediaone.net>
  3 siblings, 2 replies; 12+ messages in thread

From: Gyozo Papp @ 2001-05-03 16:19 UTC (permalink / raw)
  To: Jason <gee308@mediaone.net>; pgsql-php; +Cc: pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [NOVICE] Re:  psql with PHP question
@ 2001-05-04 08:07  Andrew McMillan <andrew@catalyst.net.nz>
  parent: Philip Hallstrom <philip@adhesivemedia.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Andrew McMillan @ 2001-05-04 08:07 UTC (permalink / raw)
  To: Philip Hallstrom <philip@adhesivemedia.com>; +Cc: Chris Ryan <chris@greatbridge.com>; Jason <gee308@mediaone.net>; pgsql-php; pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [PHP] psql with PHP question
@ 2001-05-04 20:15  Joel Burton <jburton@scw.org>
  parent: Gyozo Papp <pgerzson@freestart.hu>
  1 sibling, 0 replies; 12+ messages in thread

From: Joel Burton @ 2001-05-04 20:15 UTC (permalink / raw)
  To: Gyozo Papp <pgerzson@freestart.hu>; +Cc: Jason <gee308@mediaone.net>; pgsql-php; pgsql-novice@postgresql.org; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: psql with PHP question
@ 2001-05-05 12:28  Jesus Aneiros <aneiros@ucfinfo.ucf.edu.cu>
  parent: Gyozo Papp <pgerzson@freestart.hu>
  1 sibling, 1 reply; 12+ messages in thread

From: Jesus Aneiros @ 2001-05-05 12:28 UTC (permalink / raw)
  To: Gyozo Papp <pgerzson@freestart.hu>; +Cc: pgsql-php

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.





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: psql with PHP question
@ 2001-05-05 13:45  Gyozo Papp <pgerzson@freestart.hu>
  parent: Jesus Aneiros <aneiros@ucfinfo.ucf.edu.cu>
  0 siblings, 0 replies; 12+ messages in thread

From: Gyozo Papp @ 2001-05-05 13:45 UTC (permalink / raw)
  To: Jesus Aneiros <aneiros@ucfinfo.ucf.edu.cu>; +Cc: pgsql-php

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).





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [PHP] Re:  psql with PHP question
@ 2001-11-15 22:34  Culley Harrelson <Culley_Harrelson@pgn.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Culley Harrelson @ 2001-11-15 22:34 UTC (permalink / raw)
  To: philip@adhesivemedia.com; beloshapka@mnogo.ru; +Cc: gee308@mediaone.net; pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-php

Im sure it takes more memory but my preferred method is:

$q = pg_Exec("SELECT id FROM article WHERE id=(SELECT MAX(id) FROM article)");
$data = pg_fetch_object ($q, 0);
$maxid = $data->id;
echo "The highest id is ". $maxid;

To me this is more self-documenting and easier to maintian...

culley

>>> Philip Hallstrom <philip@adhesivemedia.com> 05/03/01 08:29AM >>>
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)




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

^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2001-11-15 22:34 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-03 12:06 psql with PHP question Jason <gee308@mediaone.net>
2001-05-03 12:49 ` Tod McQuillin <devin@spamcop.net>
2001-05-03 13:43   ` Jason <gee308@mediaone.net>
2001-05-03 12:54 ` Chris Ryan <chris@greatbridge.com>
2001-05-03 15:29   ` Philip Hallstrom <philip@adhesivemedia.com>
2001-05-04 08:07     ` Andrew McMillan <andrew@catalyst.net.nz>
2001-05-03 13:05 ` Adam Lang <aalang@rutgersinsurance.com>
2001-05-03 16:19 ` Gyozo Papp <pgerzson@freestart.hu>
2001-05-04 20:15   ` Joel Burton <jburton@scw.org>
2001-05-05 12:28   ` Jesus Aneiros <aneiros@ucfinfo.ucf.edu.cu>
2001-05-05 13:45     ` Gyozo Papp <pgerzson@freestart.hu>
2001-11-15 22:34 [PHP] Re:  psql with PHP question Culley Harrelson <Culley_Harrelson@pgn.com>

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