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

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

Replies are listed 'Best First'.
Re: fetching data from MySQL
by samtregar (Abbot) on Apr 27, 2008 at 16:09 UTC
    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;

    [download]

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

    -sam

[reply]
[d/l]
Re: fetching data from MySQL
by TOD (Friar) on Apr 27, 2008 at 11:52 UTC
    a) 
    SELECT word FROM tablename ORDER BY id ASC

    [download]
    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;

    [download]
    version b) looks more complicated, but it makes you independent from the identifiers.
    --------------------------------
    masses are the opiate for religion.
[reply]
[d/l]
[select]
      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

[reply]
Re: fetching data from MySQL
by oko1 (Deacon) on Apr 27, 2008 at 15:06 UTC

    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;
}

    [download]
    
-- 
Human history becomes more and more a race between education and catastrophe. -- HG Wells
[reply]
[d/l]
Re: fetching data from MySQL
by olus (Curate) on Apr 27, 2008 at 14:05 UTC

    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.

    [download]
[reply]
[d/l]
Re: fetching data from MySQL
by frasco (Beadle) on Apr 28, 2008 at 08:09 UTC
    thanks a lot
[reply]

Back to Seekers of Perl Wisdom