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.di...@gmx.com] > Gesendet: Mittwoch, 23. Februar 2011 21:08 > An: beginners@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 = "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/
