Welcome to the most active Linux Forum on the web.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 02-19-2004, 10:57 PM   #1
LQ Newbie
Registered: Oct 2003
Distribution: Redhat 9.0
Posts: 9

Rep: Reputation: 0
Question 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);

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

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.

Old 03-21-2007, 04:31 AM   #2
Sunil Kartikey
LQ Newbie
Registered: Mar 2007
Location: Pune
Posts: 4

Rep: Reputation: 0
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.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql reading via Perl DBI ivanatora Programming 2 03-21-2007 04:03 AM
Perl - DBI PG Installation Problems TheMeteorPolice Linux - Software 2 11-23-2005 10:53 AM
perl | DBI->connect | RHEL AS 3 kaN5300 Linux - Enterprise 6 09-14-2004 08:15 AM
perl mysql DBI 400 character limit? tjtoocool Linux - Software 2 01-10-2004 04:44 AM
perl DBI prepare question mrtwice Programming 1 10-30-2003 02:12 PM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 09:30 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration