Hi Rob,
Thank you SOOOOO much for your elaborate and clear explanations - you =
definitely made my day! *bounces*
Kind regards,
Nora
> -----Ursprüngliche Nachricht-----
> Von: Rob Dixon [mailto:rob.dixon [at] gmx.com]
> Gesendet: Mittwoch, 23. Februar 2011 21:08
> An: beginners [at] perl.org
> Cc: HACKER Nora
> Betreff: Re: Dereferencing problem
>
> 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 =3D "select secondname || ' ' || firstname from =
supp_verantw_v";
> > my $sth =3D $dbh->prepare($sql); my $personen =3D
> > $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 =3D 'select firstname, secondname from supp_verantw_v';
> my $personen =3D $dbh->selectall_arrayref($sql) or die "geht net: =
$!";
>
> foreach my $person ( [at] $personen) {
> my $fullname =3D $person->[1].' '.$person->[0];
> print $fullname, "\n";
> }
>
> > my [at] personen =3D [at] $personen;
> > foreach my $person ( [at] personen ) {
> > print "<p>Dereferencing: [at] $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 [at] $personen everywhere you have [at] personen.
>
> foreach my $person ( [at] $personen) {
> print "<p>Dereferencing: [at] $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 =3D 0; $i<=3D $#personen; ++$i ) {
> > print "Person: $person->[$i]\n"; # ????????
> > }
>
> Elements of [at] $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 =3D 'select id, firstname, secondname from supp_verantw_v';
> my $personen =3D $dbh->selectall_arrayref($sql) or die "geht net: =
$!";
>
> Then you can access their values like this:
>
> foreach my $person ( [at] $personen) {
> my $id =3D $person->[0];
> my $fullname =3D $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 =3D 'select id, firstname, secondname from supp_verantw_v';
> my $personen =3D $dbh->selectall_hashref($sql, 'id') or die "geht =
net: $!";
>
> foreach my $id (keys %$personen) {
> my $fullname =3D $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 =3D 'select id, firstname, secondname from supp_verantw_v =
order by
> secondname, firstname';
> my $personen =3D $dbh->selectall_arrayref($sql, { Slice =3D> {} }) =
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-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/
