DBI fetchrow_array problems

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I have an interesting problem and wondered whether anyone has come across this before.

I am using the DBI module to simply read a mysql database table and store columns into seperate arrays. The columns are storing identification codes. However, when i print the arrays within my perl script, one value has been missed out and placed later on in the array (which obviously messes the order of things up ;-( ).

The id that has been mis-placed (M57165208) looks out of place (as, although is contains chars and numbers, it is numerically higher that those before and after it). Can anyone suggest what is going on here and/ or an alternative solution that will preserve the database order within the per script?

Here is my code and a sample of the id's:

        my $sth21= $dbh->prepare(qq{SELECT MR_M FROM MR2});
        die "Couldn't prepare queries; aborting"
            unless  defined $sth21;
        $sth21->execute();
        while (my @ary=$sth21->fetchrow_array()) {
         push @mr_m,  @ary;
        }
        my $sth22= $dbh->prepare(qq{SELECT MR_R FROM MR2});
        die "Couldn't prepare queries; aborting"
            unless  defined $sth22;
        $sth22->execute();
        while (my @ary=$sth22->fetchrow_array()) {
         push @mr_r,  @ary;
        }

# HERE I PRINT OUT WHAT I HAVE READ FROM THE DATABASE
for (my $i=0; $i<@mr_m; $i++)   {
        print "$mr_m[$i]\t $mr_r[$i]\n";
}
[download]

This is a sample of numbers from the database:

mysql> select * from MR2 limit 10;
+-----------+--------+
| MR_M      | MR_R   |
+-----------+--------+
| M13470333 | RL2591 |
| M13470334 | RL2590 |
| M13470335 | RL2588 |
| M13470339 | RL4708 |
| M13470341 | RL2584 |
| M13470342 | RL2583 |
| M57165208 | RL2582 |
| M13470344 | RL2580 |
| M13470345 | RL2579 |
| M13470346 | RL2578 |
+-----------+--------+
10 rows in set (0.00 sec)
[download]

This is how they look when printed out:

M13470333        RL2591
M13470334        RL2590
M13470335        RL2588
M13470339        RL4708
M13470341        RL2584
M13470342        RL2583
M13470344        RL2582
M13470345        RL2580
M13470346        RL2579
M13470347        RL2578
[download]

Comment on DBI fetchrow_array problems Select or Download Code

Replies are listed 'Best First'.
Re: DBI fetchrow_array problems by mbeast (Beadle) on Feb 14, 2006 at 15:12 UTC
I think you need to add 'ORDER BY MR_M ASC' in your sql statement. This tells the DB to return the data in a certain order. I think if you put the same queries (SELECT MR_M FROM MR2 & SELECT MR_R FROM MR2) you would see that the database returns different values than 'select * from MR2 limit 10' Also, you could select both columns in one query. I don't really see any reason not to, maybe you have one. Here is a short tutorial on order by: http://sqlcourse2.com/orderby.html	[reply]
Re: DBI fetchrow_array problems by duff (Parson) on Feb 14, 2006 at 15:21 UTC
Can anyone suggest what is going on here and/ or an alternative solution that will preserve the database order within the per script? It looks like you've got a mistaken idea in your head. There is no inherent order to the rows of a table in a database unless you explicitly order them. I.e. there is no such thing as "the database order". So, what you need to do is add an `ORDER BY` clause to your `SELECT` statement such that the rows are always output in the order you desire. update: I just re-read your post and it appears that you're relying on some ordering relation that isn't described in the database. For instance, perhaps you believe that the order you get from your `SELECT` statement is the order of insertion into the table. It may happen to be the insertion order, but unless you've got a field that indicates that order, there's no reliable way to retrieve rows in that order. If this is the case, then no amount of `ORDER BY` will help you, until you modify the structure of your table. duff	[reply] [d/l] [select]
Re: DBI fetchrow_array problems by McDarren (Abbot) on Feb 14, 2006 at 15:35 UTC
erm, have you tried using an "order by" clause in your MySQL query? eg: `my $sth21= $dbh->prepare(qq{SELECT MR_M FROM MR2 ORDER BY MR_M});` [download] When you do a select from MySQL with no order by, it just returns records in the same order as they were inserted. However, even with the order by clause, you may still not get the expected result. Your columns are presumably "char" or "text" types, but from the description of your problem you really want them ordered numerically. If you're familiar with your data, there is a trick you can use with MySQL to achieve this. Basically, you order the data based on a subset of the field in question. So you could do something like: `SELECT MR_M FROM MR2 ORDER BY RIGHT(MR_M,LENGTH(MR_M)-1);` [download] This would drop the first character of the string (the "M") and sort on the remaining characters - the numerics. Alternatively, you could sort the array/s as you print it/them. Although, you'd still face the same challenges with numeric vs lexical sorting. Hope this helps, Darren :)	[reply] [d/l] [select]
Re: DBI fetchrow_array problems by jZed (Prior) on Feb 14, 2006 at 17:52 UTC
As others have said, if you want the results in a certain order, you need to specify that with an ORDER BY clause. I'd also like to point out that the way you go about gathering the array is more complex than it needs to be. If you SELECT both columns and use the DBI selectall_arrayref() method, you can capture everything at once like this: `my $results = $dbh->selectall_arrayref(" SELECT MR_M,MR_R FROM MR2 ORDER BY MR_M LIMIT 10 "); for my $row(@$results){ printf "%s\t%s\n",@$row; }` [download]	[reply] [d/l]