On 4/26/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> 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 skipped
> completely.
>
> Here is the initial code used.
>
[snip]
>
> 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
>
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.
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} = done.
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
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
