Thread: access data in php
If I gather the sql results with this code
$results = pg_query($dbconn,$query);
I can check if there is no returned data with this code
$rows = pg_fetch_assoc($result);
but if I then use a while loop to display data (if there is data returned) with this code
while ($row = pg_fetch_array($result)){ . . . }
I have to execute this code a second time before the while loop
$results = pg_query($dbconn,$query);
If I do not execute the $results line a second time the while loop does not work properly.
Why is $results loosing its value when it hits the while loop?
Thanks
Marc
On Fri, Jan 2, 2009 at 10:11 AM, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> If I gather the sql results with this code
> $results = pg_query($dbconn,$query);
>
> I can check if there is no returned data with this code
> $rows = pg_fetch_assoc($result);
>
> but if I then use a while loop to display data (if there is data returned)
> with this code
> while ($row = pg_fetch_array($result)){ . . . }
>
> I have to execute this code a second time before the while loop
> $results = pg_query($dbconn,$query);
>
> If I do not execute the $results line a second time the while loop does not
> work properly.
>
> Why is $results loosing its value when it hits the while loop?
It shouldn't be. Got a complete, short sample that does this?
On Fri, 2 Jan 2009, Marc Fromm wrote:
> If I gather the sql results with this code
> $results = pg_query($dbconn,$query);
>
> I can check if there is no returned data with this code
> $rows = pg_fetch_assoc($result);
>
> but if I then use a while loop to display data (if there is data returned) with this code
> while ($row = pg_fetch_array($result)){ . . . }
>
> I have to execute this code a second time before the while loop
> $results = pg_query($dbconn,$query);
>
> If I do not execute the $results line a second time the while loop does not work properly.
>
> Why is $results loosing its value when it hits the while loop?
>
> Thanks
>
> Marc
pg_fetch_assoc behave like pg_fetch_array: it increments the internal
pointer to the current result.
So if you call it once, then pg_fetch_array will return the 2nd result in
the result set.
You can either :
- use pg_fetch_assoc($result,0) whish shouldn't increment the internal
pointer (no sure though, check http://php.net/pg-fetch-assoc)
- seek back to the first result in the result set using pg-result-seek
(http://php.net/pg-result-seek)
- use pg_num_rows to get the number of rows in your result set.
Happy new year !
--
Guillaume (ioguix) de Rorthais
On Fri, Jan 2, 2009 at 11:09 AM, <ioguix@free.fr> wrote: > pg_fetch_assoc behave like pg_fetch_array: it increments the internal > pointer to the current result. > So if you call it once, then pg_fetch_array will return the 2nd result in > the result set. Wow, I'm so used to seeing $rows = pg_num_rows() that that's what I saw up there.
My results are missing the first record as you explained.
-----Original Message-----
From: ioguix@free.fr [mailto:ioguix@free.fr]
Sent: Friday, January 02, 2009 10:09 AM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] access data in php
On Fri, 2 Jan 2009, Marc Fromm wrote:
> If I gather the sql results with this code $results =
> pg_query($dbconn,$query);
>
> I can check if there is no returned data with this code $rows =
> pg_fetch_assoc($result);
>
> but if I then use a while loop to display data (if there is data
> returned) with this code while ($row = pg_fetch_array($result)){ . . .
> }
>
> I have to execute this code a second time before the while loop
> $results = pg_query($dbconn,$query);
>
> If I do not execute the $results line a second time the while loop does not work properly.
>
> Why is $results loosing its value when it hits the while loop?
>
> Thanks
>
> Marc
pg_fetch_assoc behave like pg_fetch_array: it increments the internal pointer to the current result.
So if you call it once, then pg_fetch_array will return the 2nd result in the result set.
You can either :
- use pg_fetch_assoc($result,0) whish shouldn't increment the internal pointer (no sure though, check
http://php.net/pg-fetch-assoc)
- seek back to the first result in the result set using pg-result-seek
(http://php.net/pg-result-seek)
- use pg_num_rows to get the number of rows in your result set.
Happy new year !
--
Guillaume (ioguix) de Rorthais
This is my code:
<?php
$dbconn = pg_connect("host=localhost port=5432 user=postgres dbname=studentalerts");
if(isset($_GET["value"])){
$w_number=$_GET["value"];
}
//echo $w_number;
$query = "select first_name, last_name, alert from alert_list where w_number='$w_number'";
$result = pg_query($dbconn,$query);
if (!$result) {
echo "Problem with query " . $query . "<br/>";
echo pg_last_error();
exit();
}
$rows = pg_fetch_assoc($result);
if (!$rows){
echo "There are no alerts for $w_number!\n\n";
}else{
$result = pg_query($dbconn,$query);
$count=1;
while ($row = pg_fetch_array($result)){
echo "Alert $count: ";
echo htmlspecialchars($row['first_name']) . " ";
echo htmlspecialchars($row['last_name']);
echo "\n";
echo htmlspecialchars($row['alert']);
echo "\n\n";
$count++;
}
}
if ($w_number==""){echo "Enter a W number!\n\n";}
echo "End of line";
pg_free_result($result);
pg_close($dbconn);
?>
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, January 02, 2009 10:28 AM
To: ioguix@free.fr
Cc: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] access data in php
On Fri, Jan 2, 2009 at 11:09 AM, <ioguix@free.fr> wrote:
> pg_fetch_assoc behave like pg_fetch_array: it increments the internal
> pointer to the current result.
> So if you call it once, then pg_fetch_array will return the 2nd result
> in the result set.
Wow, I'm so used to seeing
$rows = pg_num_rows() that that's what I saw up there.
Marc Fromm wrote:
> This is my code:
> <?php
> $dbconn = pg_connect("host=localhost port=5432 user=postgres dbname=studentalerts");
>
> if(isset($_GET["value"])){
> $w_number=$_GET["value"];
> }
> //echo $w_number;
>
> $query = "select first_name, last_name, alert from alert_list where w_number='$w_number'";
>
You should probably be using code that looks like this:
$query = "select first_name, last_name, alert from alert_list where w_number='" . pg_escape_string($w_number) . "'"
Otherwise you're vulnerable to SQL Injection attacks.. For example, what happens if w_number looks like this:
' UNION ALL select usename, passwd, '1' from pg_shadow where 'a'='a
Granted, your user might not have sufficient privileges to view *that* information (of course, your app connects as
postgres,so they probably would have access to that data), but there are lots of other nifty things that an attacker
couldgather to subvert your system. One might be:
' UNION ALL select ccnumber, cid, addr1 from creditcards where 'a'='a
> $result = pg_query($dbconn,$query);
> if (!$result) {
> echo "Problem with query " . $query . "<br/>";
> echo pg_last_error();
> exit();
> }
>
> $rows = pg_fetch_assoc($result);
>
This line ( $rows=pg_fetch_assoc($result);) should be:
$rows = pg_num_rows($result)
You just want to check that there were results, right?
Every time you call pg_fetch_assoc($result) the result set is advanced
to the next row of results, so you shouldn't use this unless you want to
actually process a row of results...
Generally speaking, you might have an easier time of interfacing with
the database if you use an abstraction layer like ADODB
(http://adodb.sf.net)
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about Expert PostgreSQL, PHP, Python, and other Open Source training!
On Fri, Jan 2, 2009 at 12:40 PM, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> This is my code:
> <?php
> $dbconn = pg_connect("host=localhost port=5432 user=postgres dbname=studentalerts");
>
> if(isset($_GET["value"])){
> $w_number=$_GET["value"];
> }
You need to scrub user input. use pg_escape_string($_GET['value'])
> //echo $w_number;
>
> $query = "select first_name, last_name, alert from alert_list where w_number='$w_number'";
> $result = pg_query($dbconn,$query);
> if (!$result) {
> echo "Problem with query " . $query . "<br/>";
> echo pg_last_error();
> exit();
> }
>
> $rows = pg_fetch_assoc($result);
Change this to
$rows = pg_num_rows($result);
> if ($rows==0){
> echo "There are no alerts for $w_number!\n\n";
> }else{
> $result = pg_query($dbconn,$query);
> $count=1;
> while ($row = pg_fetch_array($result)){
> echo "Alert $count: ";
> echo htmlspecialchars($row['first_name']) . " ";
> echo htmlspecialchars($row['last_name']);
> echo "\n";
> echo htmlspecialchars($row['alert']);
> echo "\n\n";
> $count++;
> }
> }
> if ($w_number==""){echo "Enter a W number!\n\n";}
> echo "End of line";
>
> pg_free_result($result);
> pg_close($dbconn);
> ?>
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Friday, January 02, 2009 10:28 AM
> To: ioguix@free.fr
> Cc: Marc Fromm; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] access data in php
>
> On Fri, Jan 2, 2009 at 11:09 AM, <ioguix@free.fr> wrote:
>> pg_fetch_assoc behave like pg_fetch_array: it increments the internal
>> pointer to the current result.
>> So if you call it once, then pg_fetch_array will return the 2nd result
>> in the result set.
>
> Wow, I'm so used to seeing
>
> $rows = pg_num_rows() that that's what I saw up there.
>
--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis