Writing into file (how write like this ? ) 2

[JackTheRussel]

Hi,

I get data from db (fetchall_arrayref).

SELECT a,b,c from...

Now I get records from db and record should write into file like this:

record1   record2   record3
record1   record2   record3
record1   record2   record3

How can I do this ?

It's seems not to be possible when I use
fetchall_arrayref ?

What can I do ?

Thanks.

 

[stevexff]

Jack

I gave you an example of how to do this in your other posting thread219-1322101

Didn't it work?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Steve.

Yes it worked fine.

But the example you gave me works like this:

record1 record1 record1
record2 record2 record2

But now I have situation where program should write records like this:

record1 record2 record3
record1 record2 record3
record1 record2 record3

But I havent' managed to do it.

 

[stevexff]

OK. A couple of questions for clarification:
[ol][li]Does 'record' in this instance mean 'all the columns from the row retreived from the table'? In which case, what column separator/delimiter should it use?[/li][li]Should the example you've given actually read

record1  record2  record3
record[red]4[/red]  record[red]5[/red]  record[red]6[/red]
record[red]7[/red]  record[red]8[/red]  record[red]9[/red]

?[/li][/ol]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Sorry about bad examples.

1. Example what I mean about record

SELECT fname,lname,date_of_birth FROM table;

fname  |  lname     |date_of_birth
--------------------|-------------
John   |   Doe      |1980-11-02    
Mike   |   Monroe   |1975-01-02
Diana  |   Hewitt   |1974-05-06

One record is:

John  Doe  1980-11-02

2. No.

What I mean is:

John         Mike        Diana 
Doe          Monroe      Hewitt
1980-11-02   1975-01-02  1974-05-06

I hope this clarifies.

 

[stevexff]

Aah. Yes, it's do-able, but unless you have some specific need, this kind of thing breaks down pretty quickly when you have large numbers of records. From a UX perspective, users are more comfortable with vertical scrolling than horizontal, and printing becomes an issue.

However, if that is what you want...(untested).

my $cols = 0;

foreach (@$ref) {
   $cols = @$_ if ($cols < @$_);
}

for (my $i = 0; $i < $cols; $i++) {
   my @line;
   push @line, @$_[$i] foreach (@$ref);
   print join("\t", @line), "\n";
}

The first foreach loop may not be necessary; don't know if DBI can return a jagged array or not...


Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Once again.

Thanks Steve.

 

[stevexff]

Did it work? Like I said, it's untested as I don't have perl on this PC.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Yes it worked.

and It needs the first foreach loop.

Thanks steve.

 

[stevexff]

If you just comment out the first foreach, then you'd have to set $cols = 3 on line 1.

However, with the loop in place, it's self-adjusting and works for any number of columns. So if you were to change your query, it would still work...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Hi.

I got still problems with writing the file so I didn't want to start new thread.

Here is some backround information of my program:

I always run two sql-querys. But they aren't always same queries:

if ($x == 1){
select a,b,c,d,e... from table1, table 2
}
else {
select e,f,g from table1, table 2
}

if ($y == 1){
select q,w,e,r,t ... from table1, table3
}
else {
select q,y ... from table1, table3
}

So first I check which are $x and $y values and then I run querys and then I write results into file:

my $sth = $conn->prepare($sql_string2);
$sth->{RaiseError} = 1;
$sth->execute(); 
my $ref1;
$ref1 = $sth->fetchall_arrayref;

my $sth = $conn->prepare($sql_string);
$sth->{RaiseError} = 1;
$sth->execute(); 
my $ref;
$ref = $sth->fetchall_arrayref;

my $cols = 0;
my $cols2 = 0;

foreach (@$ref1) {
   $cols2 = @$_ if ($cols2 < @$_);
}

foreach (@$ref) {
   $cols = @$_ if ($cols < @$_);
}

open (CH, ">>$file") or die "Couldn't open $file: $!";

for (my $i = 0; $i < $cols; $i++) {
   my @line;
   push @line, @$_[$i] foreach (@$ref);
   print CH join("\t", @line), "\n";

}
for ($j = 0; $j < $cols2; $j++) {

   my @line2;
   push @line2, @$_[$j] foreach (@$ref1);
   print CH join("\t", @line2), "\n";
}


close CH;

Well actually this works fine, and file looks like this:

record1   record2 recordn
record1   record2 recordn
record1   record2 recordn

But now I should write titles into file. And titles aren't always same. Example If $y == 0 and $x == 0
then titles are:

SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn

But if example $x == 1 and $y == 0
then there would be more titles than previous example:

SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn
SomeTitle: record1  record2 recordn

So now I would need some suggestions how I should do this ?

 

[stevexff]

I've already given you some code that builds up each line by transposing the query result. So all you need is to add a new row to the start of the returned values, with the titles in it.

Somewhere in your code you work out which queries you are going to run, at which point presumably you know what titles you need. So

# previously, in the guts of your code

my $t1 = ["Title 1", "Title 2", "Title 3"];

# later

[red]unshift @$ref, $t1;[/red]

for (my $i = 0; $i < $cols; $i++) {
   my @line;
   push @line, @$_[$i] foreach (@$ref);
   print CH join("\t", @line), "\n";

}

Or something similar.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Thanks steve.

Unshift was new thing for me

 

[stevexff]

shift, unshift, push, and pop are all functions for array manipulation. AFAIK they all call splice under the covers, with which you can do almost anything to an array.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]