MySQL Perl DBI question
 

New to Linux and loving it.

I will try to provide enough detail to ask a question to get an answer. If I don't do it, let me know and I will add some more detail.

I am trying to use MySQL and Perl to dynamically create a webpage. I create two different queries something like this:

my $query1 = "SELECT wo_number, location FROM Work_Order WHERE complete=\'no\' ORDER BY location";
my $sth1 = $dbh->prepare($query1);
$sth1->execute();

my $query2 = "SELECT wo_number,update_info FROM Updates";
my $sth2= $dbh->prepare($query2);
$sth2->execute();

I am trying to print the work order on the web and if there are any updates print them along with it. I will leave out the gory details but it goes something like this.

while(my $row_wo = $sth1-> fetchrow_arrayref) {

..............print the work order

while(my $row_updates = $sth2->fetchrow_arrayref) {
if ($row_wo->[0] == $row_updates->[0]) {
...............print the update
}
}
}

Obviously I left alot out. My problem is that it only seems to run the second while statement once. It does not get to the second nested print statement again because all of the remaining work orders just spill out without any updates showing again. I figured that because of the scope of the second while statement it would "reset" the second fetchrow statement and go again. It does not.
I know it is probally not the most efficient way to do this but I was looking for a cheap and dirty solution. If their is a better way let me know or if someone has the reason why this does not work it would be appreciated. I have been scouring the web for a solution but can't seem to find one.
If this should work let me know and I will post the whole section of code to see if someone can tell me where I am screwing up.

Thanks,
Gene

see buddy you cannot reset second while loop ref values..
its better use for loop for each value of $row_wo and $row_updates

my $numFields = $sth->{'NUM_OF_FIELDS'};
while (my $ref = $sth->fetchrow_arrayref) {

for (my $i = 0; $i < $numFields; $i++) {

print $$ref[$i];
}

use fetchrow_arrayref once for both the tables separately,not as a subset of one into other.

and using $ref use for loop to get each value and then compare.
it should work..

also on the place of my $ref = $sth->fetchrow_arrayref u can use @data = $sth->fetchrow_array()
something like
while (@data = $sth->fetchrow_array()) {
my $firstname = $data[1];
my $id = $data[2];
print "\t$id: $firstname $lastname\n";
}

if you still not able to do just send me your whole code i'll rit it for you.