fetching data from MySQL

[frasco]

Dear Monks, I would like to fetch data from a MySQL database. I read perlmonks tutorials and other sources but still I have some problems. I have the following table structure holding words of a splitted text file:

|id   |line_n  |word          |word_position
--------------------------------------------
|1    |1       |Lorem         | 1
|2    |1       |ipsum         | 2
|3    |1       |dolor         | 3
|4    |1       |sit           | 4
|5    |1       |amet,         | 5
|6    |2       |consectetuer  | 1
|7    |2       |adipiscing    | 2
|8    |2       |elit.         | 3
|9    |3       |Phasellus     | 1
|10   |3       |non           | 2
|11   |3       |erat          | 3
|12   |3       |...           | 4

By retrieving data from db I wold like that my output would be the same as follow:

line 1    Lorem ipsum dolor sit amet,
line 2    consectetuer adipiscing elit.
line 3    Phasellus non erat ... 

That is the original layout of my text file. Well, among different fetching methods I don't know exactly which one is the most useful for such a purpose. Moreover I don't know yet how to write a good code; but this is another problem. thanks, Francesco

[samtregar]

I'd probably do what others have suggested, fetch the rows separately and combine them in Perl. But you might be interested to know that MySQL can do this all for you:

SELECT CONCAT("Line ", line_n, "    ", 
              GROUP_CONCAT(word ORDER BY word_position SEPARATOR " "))
FROM words 
GROUP BY line_n
ORDER BY line_n;

GROUP_CONCAT is a powerful tool indeed - very useful in report building.

-sam

[TOD]

a)

SELECT word FROM tablename ORDER BY id ASC

b)

ALTER TABLE tablename ADD INDEX(line_n);
ALTER TABLE tablename ADD INDEX(word_position);
ALTER TABLE tablename ADD INDEX(line_n,word_position);
SELECT word FROM tablename USE INDEX(line_n,word_position) ORDER BY (l
+ine_n*100+word_position) ASC;

version b) looks more complicated, but it makes you independent from the identifiers.

[samtregar]

There's no need to do multiplication tricks in the ORDER BY - MySQL supports ordering by multiple columns. Also, due to the way MySQL uses indexes you don't need separate (line_n) and (line_n,word_position) indexes. A query that could use the first index will be able to use the second one just as well.

-sam

[oko1]

You should read up on DBI, and select a function that returns the data you need in the order you need it. For example:

#!/usr/bin/perl -w
use strict;
use DBI;
$|++;
my $user = "username";
my $pass = "password";
my $dbnm = "database";
my $table = "table";

my $dbh = DBI->connect("DBI:mysql:$dbnm", $user, $pass);
my $ret = $dbh->selectcol_arrayref("select line_n, word from $table", 
+{ Columns => [ 1, 2 ] });
$dbh->disconnect;

my %seen;
my @list = @$ret;
while (@list){
    my $line_n = shift @list;
    my $word = shift @list;

    print $seen{$line_n}++ ? " " : "\n$line_n: ";
    print $word;
}

[olus]

Something like this could do the work:

#connect to the database and issue the following query;
#select * from tablename order by line_n,word_position asc

#then loop through the results and print the lines
my (@row, $row);
my $line_no = -1;
my $line = '';
while(@row = $sth->fetchrow_array) {
    if($line_no == $row[1]) {
        $line .= " $row[2]";
    } else {
        print "Line $line_no $line"; unless ($line_no == -1);
        $line = '';
        $line_no = $row[1];
    }
}

# note that this is untested code.

[frasco]

thanks a lot