my %results = ();
@results{ @row[5..10] } = @row[11..16];
[download]

I'm not sure what to tell you about those totals though. I can tell you that this is a very strange way to design a database... having "column names" stored in columns. I would suggest a rewrite and then you can use fetchrow_hashref a lot easier. But that's just me.

--
perl -e "print qq/just another perl hacker who doesn't grok japh\n/"
simeon2000|http://holdren.net/

...
my $SELECT = qq/select field1, value1 , field2, value2 from table/;
my $sth = $dbh->prepare($SELECT);
$sth->execute;
while( my $row = $sth->fetchrow_arrayref ) {
           my %hash = @$row;
           .....
}
[download]

First off, I think some of the ugliness that you're stuck with here may be a result of the layout of the database that you're working with. It seems like it could use some more normalization, but without actually knowing the contents of the votes table I can only guess at it.

My guess is that the values in columns 5-10 (your hash keys) are some kind of label or identifier for choices that users vote on. Then the columns 11-16 contain the number of votes for the corresponding value in columns 5-10.

If you have any control over how this table looks, I would change it. Though only if you will be working with it frequently. There's nothing more annoying than a poorly formed database. It'll bite you in ways you never imagine when you start.

Regardless, there are ways that you can clean up the code in Section 1 above. First thing would be to NOT use "SELECT * ...". It doesn't tell you anything about what you're actually getting out of the table and you have to guess which position in the array holds the value you want to use. I highly recommend making a longer query that specifies exactly which columns you need. If you have to look at the code again in a month, will you remember the column configuration from that table? If you specify the columns you're selecting, you won't have to.

Next I would use some named variables to hold the values returned by your query. Pumping the row into an array is convenient, but as you've mentioned it's ugly, especially when dealing with 16+ columns. If I were coding this, I would use something like the following:

my ($choice1,$choice2,$choice3,$choice4,$choice5,$choice6);
my ($votes1,$votes2,$votes3,$votes4,$votes5,$votes6);

while( ($choice1, $choice2, $choice3, $choice4, $choice5, $choice6, $v
+otes1, $votes2, $votes3, $votes4, $votes5, $votes6) = $sth_1->fetchro
+w_array )
{
 .
 .
[download]

This will only work, of course, if you took my advice above and only selected the choice and votes columns that you need rather than doing SELECT *.

Populating the hash looks much nicer, too using these variables rather than the uninformative row array values:

 
%results = ( $choice1 => $votes1,
             $choice2 => $votes2,
             $choice3 => $votes3,
             .
             . );
[download]

YIKES, I wrote way too much! Hopefully some of it might be useful, somehow! I guess I just really don't want to get to work yet this morning.

~CubicSpline
"No one tosses a Dwarf!"

Suggestion for your first question: You don't seem to be using all the rows that are returned from your query, so first, rather than selecting '*' you should select only the columns you want by name (since I don't know your column names this is only an example)

SELECT column1,column2,column3 FROM table WHERE column4=?";
[download]

Second suggestion has to do with the '?' on the end of my example. This is called a place holder and you should always use them. Why? Because DBI will then escape any bad characters for you. Also using this method your query will be cached and you can call it will lots of different parameters more than once without rebuilding the query, saving on coding time and speed for your program. (they are great in loops). You add the parameters by passing what you want in that question mark with the execute statement.
A 'gotcha' of placeholders however is that you can only use them to represent values, you can't use them to represent table or column names or other pieces of a statement. (in other words something like this:

 
SELECT ? FROM ? WHERE ?=?
[download]

Finally, instead of using $sth->fetchrow_array() use $sth->fetchrow_hashref(). This will return a reference to the hash you're looking for. The keys to this hash are your column names. So your final code would look something like this:

my $query = "SELECT column1,column2,column3 FROM table WHERE column4=?
+";
my $sth = $dbh->prepare($query);
$sth->execute($value_for_placeholder) or die $sth->errstr();
my $hash_ref = $sth->fetchrow_hashref();

# if its easier for you, you can dereference that hash_ref:
my %hash = %$hash_ref;
[download]

Voila! and there you have it. I hope that helps!

Chris

item_no (the number of the poll in the database)
date_begun (the date the vote was added)
date_ended (the date the poll stopped)
begun_by (the username of the person who created the poll)
description (the text above the vote)
option_1_t -> option_6_t (6 columns containing the text of the vote op
+tion)
option_1_n -> option_6_n (6 columns containing the numbers attached to
+ vote options)
[download]

Neither a perl problem, nor a DBI problem

Remove the options columns, and create a secondary table. This will have 4 columns, and (upto) 6 rows for each entry in the main table.

--
Tommy
Too stupid to live.
Too stubborn to die.

G'day all

Would you care to elaborate ?

ie. What is a secondary table ?
How is one created ?
How do you link it to the main database ?
Or is it not linked and merely referred to ?

Cheers
lagrenouille