asked on

SQL query within fetchrow

Hello,

Im writing a perl script and need to select some rows from a table, then run a sql query based on the information received for each row.

ie.

$SQL = "SELECT * FROM table";
&Do_SQL;

while($pointer = $sth->fetchrow_hashref)
{

$gross = $pointer->{'gross'};
$name += $pointer->{'name'};
if($gross eq "A")
{
$SQL = "SELECT title FROM table WHERE bla bla";
&Do_SQL;
while($pointer = $sth->fetchrow_hashref)
{
$title = $pointer->{'title'};
}
}
}

But that wont work, so how can it be done?

kandura

It won't work, because the inner statement handle $sth invalidates the outer $sth. That's the price you pay for doing the wrong kind of abstraction with your Do_SQL subroutine.

I'll give you one line of advice. You can come back with questions if you get your script to run again with that line added:

use strict;

Put it at the top of your script.

ahoffmann

simply get rid of the inner while loop by specifying a proper SELECT statement ;-)

sharpmail

ASKER

what do you mean a proper select statment? and what am i doing wrong with the Do_SQL sub?

sharpmail

ASKER

I have managed to make it work by making another sub of Do_SQL and renaming $sth to $sth1 so they dont invalidate each other. but if there is a better way, i would like to know ;)

ASKER CERTIFIED SOLUTION

kandura

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharpmail

ASKER

ah I see what you mean! now that is much tidier than my idea. With reference to the global var whats wrong with using that? and what do you mean by hiding the wrong kind of complexity?

kandura

Global variables are bad (in general), because they make it harder to track bugs; they can cause side effects; they break encapsulation. By keeping every variable in as small a scope as possible, you keep everything nicely together; it makes your code more modular, easier to read and maintain, and generally better.

As for hiding the wrong complexity: you can no longer pass specific attributes, or use placeholders to prepare and execute. That means you're missing out on all kinds of neat features; plus it will be harder to catch errors, because they now all seem to come from Do_SQL.

Anyway, you're helped for now, and that is what counts. Maybe all this is a bit over your head right now; you'll become more experienced at programming over time, and then you'll find those things out anyway.

sharpmail

ASKER

well the var SQL is always reset in this instance so wont cause a problem, but I realise what your saying. As for getting better, maybe not, this is year 5 of programming, while I can write and do what I need to do in order to run my site im still using the basics of perl the site is run completly in perl and could be done more efficiently but as always time isnt on my side!

Thank you for your assistance!

Danny