in reply to Fetching data from DB and complex data structures

Just build the structure instead of printing the values obtained from the database. I used SQLite to test: 
#! /usr/bin/perl
use warnings;
use strict;

use DBI;
use Data::Dumper;

my $dbfile = '1.db';
my $db = 'DBI'->connect("dbi:SQLite:dbname=$dbfile", q(), q());
END { unlink $dbfile }

$db->do('CREATE TABLE text (id INT, line_n VARCHAR, word VARCHAR, word
+_position INT)');

my $insert = $db->prepare('INSERT INTO text VALUES(?, ?, ?, ?)');
{   no warnings 'qw';
    $insert->execute(@$_) for [qw[1  a1 Lorem        1]],
                              [qw[2  a1 ipsum        2]],
                              [qw[3  a1 dolor        3]],
                              [qw[4  a1 sit          4]],
                              [qw[5  a1 amet,        5]],
                              [qw[6  b2 consectetuer 1]],
                              [qw[7  b2 adipiscing   2]],
                              [qw[8  b2 elit.        3]],
                              [qw[9  c3 Phasellus    1]],
                              [qw[10 c3 non          2]],
                              [qw[11 c3 erat         3]];
}

[download]

Now the real work: 

my $select = $db->prepare('SELECT line_n, word, word_position FROM tex
+t');
$select->execute;
my $text;
while (my ($line, $word, $pos) = $select->fetchrow_array) {
    $text->{$line}[$pos - 1] = $word;
}
print Dumper($text);

[download]
($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

[download]

Replies are listed 'Best First'.
Re^2: Fetching data from DB and complex data structures
by frasco (Beadle) on Dec 14, 2015 at 16:19 UTC

    Thank you very much.

    Just a question. Since I'm pretty new to Perl I cannot understand that "strange construct" in the while loop:

    $text->{$line}[$pos - 1] = $word;

    Please, can you give some more details on it?

    Then here come the second question/problem. As far I can see keys are the original line numbers and values are references to arrays containing the words of each line. In that case, if I'm not in wrong, keys/lines do not respect the original order of my text. Is it true?

[reply]
[d/l]
[reply]
[d/l]
        $text->{$line}[$pos - 1] = $word;
       ^ ^      ^
       | |      |
       | hash   |
dereference    array

      [download]

      I used $pos - 1 as positions start from 1, but array indices start from 0.

      Note that the bracket types are the same as when creating the data: 

      $VAR1 = {
          'c3' => [
                    'Phasellus',
                    'non',
                    'erat'
                  ],
          'b2' => [
                    'consectetuer',
                    'adipiscing',
                    'elit.'
                  ],
          'a1' => [
                    'Lorem',
                    'ipsum',
                    'dolor',
                    'sit',
                    'amet,'
                  ]
        };

      [download]
      ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

      [download]
[reply]
[d/l]
[select]
        Thank you very much for the links and for the fish as well. If i can, what about the original order of the text? a1->b2->c3? Unfortunately, since the lines are indexed in a more complicated fashion than the one I reported, I cannot simply sort hash keys. In fact the original order is governed by Postgres only.
[reply]

In Section Seekers of Perl Wisdom