Dear Monks, some times ago I asked for Monks wisdom in order to find a convenient way to fetch data from a DB table. I had the following table structure holding words of a splitted text file:

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

By retrieving data from db I needed that my output would be the same as follow (without GROUP_CONCT and similar database functions):

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.

A wise monk suggested this code to print the output:

my $sql = qq(select * from table order by id);

my $data_col = $dbh->selectcol_arrayref($sql, { Columns => [ 2, 3 ] })
+;

my %seen;
my @list = @$data_col;

while (@list){
    my $line_n = shift @list;
    my $word = shift @list;
    
    if ($seen{$line_n}++) {
        print " ";
    } else {
        print "\n$line_n - ";
    }
    print $word;
}
[download]

Of course, It was ok until I had to immediately print the output, but I cannot figure out how to store retrieved data in a complex data structure for further use (i.e. to use such output as json, etc). For instance:

my $text = [
            "myText_01",
                [
                    a1, ["Lorem", "ipsum", "dolor", "sit", "amet,"]
                ],
                [
                    b2, ["consectetuer", "adipiscing", "elit."]
                ],
                [
                    c3, ["Phasellus", "non", "erat"]
                ],
                [
                    4, ["...", "..."]
                ]
            ];
[download]

thanks, Francesco

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.