rows returned and while statement

Ok I have an issue where I run an sql query against a database, and want
to evaluate whether or not an entry occurs for a given user, if NOT then
this run will be the first entry. Ok the problem occurs in the while
statement, everything works as expected if the user already has an entry
in the DB, however if there is NOT an entry then the while loop is skipped
completely.



Here is the initial code used.



while(my @row = $sth->fetchrow_array) {



if($row[4] == "") {

$first_grace_count = $row[9];

&FirstOffense;

}elsif($row[12] == "") {

$second_grace_count = $row[17];

&SecondOffense;

}elsif($row[20] == "") {

$third_grace_count = $row[25];

&ThirdOffense;

}else {

print "Offender already has 3 or more offenses and should
already be terminated\n";

}





The sub FirstOffense gathers data and inserts it into the database as the
first entry.



So how can I get the FirstOffense sub to run if no rows are returned from
the initial query?



Chris Hood

Investigator Verizon Global Security Operations Center

Email:  <mailto:christopher.l.hood@verizon.com>
christopher.l.hood@verizon.com

Desk: 972.399.5900

Verizon Proprietary



NOTICE - This message and any attached files may contain information that
is confidential and/or subject of legal privilege intended only for the
use by the intended recipient.  If you are not the intended recipient or
the person responsible for delivering the message to the intended
recipient, be advised that you have received this message in error and
that any dissemination, copying or use of this message or attachment is
strictly forbidden, as is the disclosure of the information therein.  If
you have received this message in error please notify the sender
immediately and delete the message.

On 4/26/05, christopher.l.hood@verizon.com
<christopher.l.hood@verizon.com> wrote:
> Ok I have an issue where I run an sql query against a database, and want
> to evaluate whether or not an entry occurs for a given user, if NOT then
> this run will be the first entry. Ok the problem occurs in the while
> statement, everything works as expected if the user already has an entry
> in the DB, however if there is NOT an entry then the while loop is skippe=
d
> completely.
>=20
> Here is the initial code used.
>=20
[snip]
>=20
> The sub FirstOffense gathers data and inserts it into the database as the
> first entry.
>=20
> So how can I get the FirstOffense sub to run if no rows are returned from
> the initial query?
>=20
> Chris Hood
>=20

Well, yes, the database can only return rows that exist.  It's a
little bit difficult to suggest a solution without seeing the
structure of the query you're submitting, but braodly speaking, you
have a few choices:

1) Run a separate query for each user.  Then if the query returns a
value, you update the record; if it doesn't, you create the record.=20
If the number of users is large, this can significantly increase load
on the DB server.

2)  include a table (like the table of all users on the system) in a
join or union, that you know for certain the user must have an entry
in.

3)  Keep an array that you update on each iteration of the while loop
to ad the user being processed that iteration.  When the whil loop
exits, compare the two loops (grep) and go back and create a record
for anyone who's in the first list but not the second.

4) keep a hash called %bad_guys with all of the names you query.  On
each iteration through the loop, do $bad_guys{$current_name} =3D done.=20
Then at the end, do:

foreach (keys %bad_guys) {
FirstOffense() unless $bad_guys{$_} eq 'done';
}

I'm sure there are others.  But the basic point to keep in mind is
that the database server won't return any information about rows it
doesn't have.

HTH,

--jay

You could use this after the while loop:

if ($sth->rows() == 0) {
# First offense code
}

Giff

-----Original Message-----
From: christopher.l.hood@verizon.com [mailto:christopher.l.hood@verizon.com]

Sent: 26 April, 2005 11:24
To: beginners@perl.org
Subject: rows returned and while statement

Ok I have an issue where I run an sql query against a database, and want to
evaluate whether or not an entry occurs for a given user, if NOT then this
run will be the first entry. Ok the problem occurs in the while statement,
everything works as expected if the user already has an entry in the DB,
however if there is NOT an entry then the while loop is skipped completely.



Here is the initial code used.



while(my @row = $sth->fetchrow_array) {



if($row[4] == "") {

$first_grace_count = $row[9];

&FirstOffense;

}elsif($row[12] == "") {

$second_grace_count = $row[17];

&SecondOffense;

}elsif($row[20] == "") {

$third_grace_count = $row[25];

&ThirdOffense;

}else {

print "Offender already has 3 or more offenses and should
already be terminated\n";

}





The sub FirstOffense gathers data and inserts it into the database as the
first entry.



So how can I get the FirstOffense sub to run if no rows are returned from
the initial query?



Chris Hood

Investigator Verizon Global Security Operations Center

Email:  <mailto:christopher.l.hood@verizon.com>
christopher.l.hood@verizon.com

Desk: 972.399.5900

Verizon Proprietary



NOTICE - This message and any attached files may contain information that is
confidential and/or subject of legal privilege intended only for the use by
the intended recipient.  If you are not the intended recipient or the person
responsible for delivering the message to the intended recipient, be advised
that you have received this message in error and that any dissemination,
copying or use of this message or attachment is strictly forbidden, as is
the disclosure of the information therein.  If you have received this
message in error please notify the sender immediately and delete the
message.