Reading data from mySQL into hashes and manipulation of hashes

[lagrenouille]

G'day all

What I need to do:
1. Extract some values from a mySQL database and store them in an associate array.

my $sth_1 = $dbh->prepare("SELECT * FROM vote WHERE item_no='$last_ite
+m'");
$sth_1->execute();
while (my @row = $sth_1->fetchrow_array())
{
#### Define variables from the mySQL output.
my $description = $row[4];
my %results = ("$row[5]","$row[11]","$row[6]","$row[12]","$row[7]","$r
+ow[13]","$row[8]","$row[14]","$row[9]","$row[15]","$row[10]","$row[16
+]");

This is how I have been doing it, and it works... however I'm sure there is a more efficient method of doing it... I just can't nut it out.
Any suggestions ?

2. Then I need to calculate the total of the votes entered and then calculate these in percentages using the total (stored in $row[11] -> $row[16])

#### Turn the output into percentages.
my $total = $results{"$row[5]"} + $results{"$row[6]"} + $results{"$row
+[7]"} + $results{"$row[8]"} + $results{"$row[9]"} + $results{"$row[10
+]"};
$results{"$row[5]"} = ($results{"$row[5]"} / $total) * 100;
$results{"$row[6]"} = ($results{"$row[6]"} / $total) * 100;
$results{"$row[7]"} = ($results{"$row[7]"} / $total) * 100;
$results{"$row[8]"} = ($results{"$row[8]"} / $total) * 100;
$results{"$row[9]"} = ($results{"$row[9]"} / $total) * 100;
$results{"$row[10]"} = ($results{"$row[10]"} / $total) * 100;

I don't think this is working... It is also very ugly and innefficient as with the first part of my question. Could anyone explain why it isn't working ? (the values are not being given data to store... uninitialised)

3. I then need to sort the associate array using the percentage as the item to sort by, so that the highest percentage is the first item in the associate array.
This I have little or no idea about... so any comments suggestions most appreciated.

Thanks in advance
lagrenouille

Edit: Added <code> tags. larsen

[simeon2000]

For #1 you could shorten that "result" hash code by using array slices:

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

You also don't need to quote your "$row[xyz]" declarations.

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/

[reyjrar]

I'd be interested to know why you're pulling columns in the database out and using them as both keys and values in the hash. It doesn't seem to be the most efficient way to do things. What does your database table look like? I was going to suggest reading up on DBI, specifically the statement handle method fetchrow_hashref(). After I went back and actually read your code a little close, I realized this likely wouldn't help you. If you have a table of votes you might consider setting up the tables so its a little eaiser to make data retrieval much easier.

Also note that the "SELECT * FROM" using fetchrow_array and fetchrow_arrayref are not very maintainable. For instance, if you were to change the order of fields while altering the table to add a new column, you would need to redo every select statement and the stuff around it. If you want to grab all the fields, consider using fetchrow_hashref, which is slightly slower than fetchrow_arrayref but it MUCH more maintainable. Personally, I'd recommend not pulling out more data than necessary, and explicity specifying the columns to pull out in the select statement. This allows you to control the order of the array. This would be useful to your code taking it from the above to:


...
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;
           .....
}


The rest of that code, I can't make heads or tails of why you'd want to store things like that. I can provide more help if I knew what columns you were pulling out and why you're handling them the way you are.

-brad..

[CubicSpline]

Greetings lagrenouille!

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 )
{
 .
 .

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,
             .
             . );

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!"

[cfreak]

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=?";

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 ?=?

would not work).

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;

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

Chris

Lobster Aliens Are attacking the world!

[lagrenouille]

G'day all

This is one of the many reasons I visit this site and community, they are damn good and pleased to assist. Thanks once again!

The current set up of the database is as follows:

1 table of a mySQL database. (each line is a column)

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)

This is the current layout of the database...

As you've probably guesses, I'm fairly new to DBI and perl in general, so any pointers and documentation recommendations gratefully accepted!

How could the database be better designed ?

Thanks again
lagrenouille

[tommyw]

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.

[lagrenouille]

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