Hi Nora
On 23/02/2011 12:53, HACKER Nora wrote:
> Hi,
>
> I want to work with data from my database. The following select and
> dereferencing for display is fine:
>
> my $sql = "select secondname || ' ' || firstname from supp_verantw_v";
> my $sth = $dbh->prepare($sql);
> my $personen = $dbh->selectall_arrayref($sql) or die "geht net: $!\n";
First of all, your prepare call is wasted here. You never use the $sth
again, and selectall_arrayref does its own prepare (and execute) in
order to fetch the data. You can just leave this line out or, if your
program performs this same select several times, it also accepts a
ready-prepared statement handle in place of an SQL string.
Also, I would be inclined to use DBI to fetch the raw data from the
database and let Perl manipulate it, rather than use the SQL engine
format the values for you. Something like:
my $sql = 'select firstname, secondname from supp_verantw_v';
my $personen = $dbh->selectall_arrayref($sql) or die "geht net: $!";
foreach my $person (@$personen) {
my $fullname = $person->[1].' '.$person->[0];
print $fullname, "\n";
}
> my @personen = @$personen;
> foreach my $person ( @personen ) {
> print "<p>Dereferencing: @$person\n</p>\n";
> }
There is no need to to copy the anonymous array to a named array. It is
wasteful of time and memory as it makes two identical instances of the
data in memory. Simply use @$personen everywhere you have @personen.
foreach my $person (@$personen) {
print "<p>Dereferencing: @$person\n</p>\n";
}
> Now I want to alter the foreach-loop to a for-loop (because I need the
> index number) - but I don't know how to dereference over two levels:
>
> for ( my $i = 0; $i<= $#personen; ++$i ) {
> print "Person: $person->[$i]\n"; # ????????
> }
Elements of @$personen are accessed using $personen->[0],
$personen->[1]... etc. Each of these, in turn, are references to arrays,
and the elements of those arrays are reached with a second level of
indexing: $personen->[0]->[0], $personen->[0]->[1]... etc. Also Perl
allows us to remove indirection arrows beyond the first, so
$personen->[0][0], $personen->[0][1]... look much tidier. (Those two
values, by the way, are fields one and two of the first record returned.)
In addition, it is usual in Perl to iterate over a range of indices, so
your loop could be written:
for my $i (0 .. $#{$personen}) {
print "Person: $personen->[$i][0]\n";
}
> Error: Using an array as a reference is deprecated at
> /opt/freeware/apache/share/cgi-bin/edit/supportumg_neu.pl line 57.
> Global symbol "$person" requires explicit package name at
> /opt/freeware/apache/share/cgi-bin/edit/supportumg_neu.pl line 57.
You are not 'using an array as a reference', so you must have seen this
error in a previous version of your code. And '"$person" requires
explicit package name' because it should be $personen!
> Bonus question: My main goal is to have a<select> field in a HTML form.
> The data for the options comes from the above select, the "values" are
> meant to be the IDs from the same table. My approach was to issue two
> separate select statements, one for the persons' names, another one for
> their IDs - that's why I needed the for-loop for the index. I also read
> on CPAN's documentation of DBI that by using selectall_hashref it should
> be possible to store the corresponding ID and name together in a hash,
> but again, I just didn't get it to work :-(
Although it is very likely, there is no guarantee that data will come
out of a database in the same order for two separate select statements,
so you need to fetch the value of ID at the same time as the names.
That's not difficult at all - just add id (or whatever the name of your
column is) to the list of columns in the select statement, like this:
my $sql = 'select id, firstname, secondname from supp_verantw_v';
my $personen = $dbh->selectall_arrayref($sql) or die "geht net: $!";
Then you can access their values like this:
foreach my $person (@$personen) {
my $id = $person->[0];
my $fullname = $person->[2].' '.$person->[1];
:
:
}
You can use selectall_hashref instead, when you will get a hash of
hashes instead of an array of arrays. You have to specify in the call
which of the table columns you want to act as hash keys to the records
returned. You will want to use 'id', and your code will look like this:
my $sql = 'select id, firstname, secondname from supp_verantw_v';
my $personen = $dbh->selectall_hashref($sql, 'id') or die "geht net: $!";
foreach my $id (keys %$personen) {
my $fullname = $personen->{$id}{secondname}.' '.$personen->{$id}{firstname};
:
:
}
But the disadvantage of using this method is that, because hashes are
unordered, any sorting done by the database engine (using 'order by')
would be ineffective. Because the data are appearing in an HTML list of
<option> elements you will probably want to present them in name, or
possibly ID order. That means you would have to sort the records in Perl
after fetching them from the database, which isn't nice programming, and
for that reason I recommend you stick with selectall_arrayref and add an
'order by' to the SQL.
(DBI does offer a third option which offers the best of both worlds and
returns an array of hashes. If you write
my $sql = 'select id, firstname, secondname from supp_verantw_v order by
secondname, firstname';
my $personen = $dbh->selectall_arrayref($sql, { Slice => {} }) or die "geht
net: $!";
then you will get an (ordered) array of hashes. But I won't go any
further into that for fear of confusing you.
> Any help GREATLY appreciated - what a frustrating day today :-(
I hope this helps you to complete your task, and makes tomorrow a better
day!
- Rob
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
