DBD retrieve question

JSchmitz has asked for the wisdom of the Perl Monks concerning the following question:

I am using DBD::MySQL to connect to a database and retrieve some data. I am able to do so with this bit here:

# Now retrieve data from the table.
my $sth = $dbh->prepare("SELECT * FROM Success_Matrix");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
    print "Totals by day: id = $ref->{'id'}, Total_Number_Failures = $
+ref->{'Total_Number_Failures'}\n";
  }
  $sth->finish();
[download]

this returns:

Totals by day: id = , Total_Number_Failures = 47
Totals by day: id = , Total_Number_Failures = 47
Totals by day: id = , Total_Number_Failures = 52
Totals by day: id = , Total_Number_Failures = 42
Totals by day: id = , Total_Number_Failures = 39
Totals by day: id = , Total_Number_Failures = 68
Totals by day: id = , Total_Number_Failures = 48
Totals by day: id = , Total_Number_Failures = 39
Totals by day: id = , Total_Number_Failures = 38
Totals by day: id = , Total_Number_Failures = 51
Totals by day: id = , Total_Number_Failures = 39
Totals by day: id = , Total_Number_Failures = 36
Totals by day: id = , Total_Number_Failures = 44
Totals by day: id = , Total_Number_Failures = 52
Totals by day: id = , Total_Number_Failures = 45
Totals by day: id = , Total_Number_Failures = 79
[download]

what I would like to be able to do is add another field (or) return two rows at once that are connected like join the date field to each row.

any ideas?

Jeffery

Comment on DBD retrieve question Select or Download Code

Replies are listed 'Best First'.
Re: DBD retrieve question by cbrandtbuffalo (Deacon) on Mar 31, 2005 at 17:09 UTC
I'm not sure exactly what you are asking, but I'll give it a shot. If you want to print values for another column in the table, add a statement like: `print "New column:" . $ref->{'other_column'};` [download] The fetchrow_hashref statement creates a hashref with keys for each column in your original SQL statement. Since you are doing a 'SELECT ' you are getting all columns from that table. You just need to know the name of the column to access. As a general rule, you probably want to avoid 'SELECT ' in important code since changes to the table may break your code. It's best to just select the specific columns you want/need.	[reply] [d/l]

Replies are listed 'Best First'.

Re: DBD retrieve question
by cbrandtbuffalo (Deacon) on Mar 31, 2005 at 17:09 UTC

print "New column:" . $ref->{'other_column'};
[download]

The fetchrow_hashref statement creates a hashref with keys for each column in your original SQL statement. Since you are doing a 'SELECT *' you are getting all columns from that table. You just need to know the name of the column to access.

As a general rule, you probably want to avoid 'SELECT *' in important code since changes to the table may break your code. It's best to just select the specific columns you want/need.

[reply]
[d/l]