CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Array Question

 



adrockjames
stranger

Jun 29, 2000, 1:57 PM

Post #1 of 7 (2081 views)
Array Question Can't Post

hello,

have a sql statement that gets usernames and logged in times for these users from a database. the usernames vary , but many begin with the same name:

ex.
adrock - login1 13:12:00
adrock - login2 15:00:00

for these similar user names, i wish to combine the logged in times into one record, but am having some trouble doing so.

ex:
adrock - 28:12:00

i need the code in between

$rv=$sth->execute;
while(@row = $sth->fetchrow_array) {

## $row[0] being the username and $row[1] ## being the logged in time
.
.
.
.
.
.

}

$sth->finish;
$dbh->disconnect;

any suggestions/help is much appreciated!



Kanji
User

Jun 29, 2000, 5:40 PM

Post #2 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

You should see if you can't do this in your SQL statement, rather than in Perl. Check your database's documentation for the prefered method, but in MySQL it'd be something ...

<BLOCKQUOTE><font size="1" face="Arial,Helvetica,sans serif">code:</font><HR>

SELECT username,
SEC_TO_TIME(
SUM(
TIME_TO_SEC( period )
)
) AS time
FROM mytable
GROUP BY username
ORDER BY username</pre><HR></BLOCKQUOTE>

Which, if you had a table such as this ...

<BLOCKQUOTE><font size="1" face="Arial,Helvetica,sans serif">code:</font><HR>

+-------------+----------+
| username | period |
+-------------+----------+
| kanji | 00:10:03 |
| adrockjames | 00:07:33 |
| adrockjames | 00:07:27 |
| kanji | 00:05:13 |
+-------------+----------+</pre><HR></BLOCKQUOTE>

... would return the following rows ...

<BLOCKQUOTE><font size="1" face="Arial,Helvetica,sans serif">code:</font><HR>

+-------------+----------+
| username | time |
+-------------+----------+
| adrockjames | 00:15:00 |
| kanji | 00:15:16 |
+-------------+----------+</pre><HR></BLOCKQUOTE>

If you really, really, really want to do it in Perl, then I'd suggest keeping a tally in the form as a hash as you iterate over the results ...

<BLOCKQUOTE><font size="1" face="Arial,Helvetica,sans serif">code:</font><HR>

my %tally;
while(my($user,$time) = $sth->fetchrow_array) {
$seconds = hhmmss_to_ss($time);
$tally{$u} += $seconds;
}</pre><HR></BLOCKQUOTE>

hhmmss_to_ss() is something I leave as an exercise to you to write. ;^)


adrockjames
stranger

Jun 30, 2000, 5:17 AM

Post #3 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

Thanks for the help!

I assumed it needed to be in perl since the usernames were not exactly the same, just began with the same name. So the tables look
like:

username | period
-------------+-------------
adrock-mail | 12:00:00
adrock-phone | 13:00:00

and what i want returned is

adrock 25:00:00

(a total time for that user from each of his/her usernames)

can i do this with the sql statement?

Thanks again!


Kanji
User

Jun 30, 2000, 11:50 AM

Post #4 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

Depends on your db implementation.

In my previous example substituting SELECT SUBSTRING_INDEX(username,'-',1) AS username, for SELECT username, will do the trick ... in MySQL anyway.

It may be easier or more convenient for you to do this in Perl if you don't know SQL, tho' as slapping a $u =~ s/-.*//; before $tally{$u} += $seconds in my example will achieve the same effect, but be (IMHO) far more obvious.


dws
Deleted

Jun 30, 2000, 3:35 PM

Post #5 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

Are these times, or durations? Adding times makes no sense.


Kanji
User

Jun 30, 2000, 4:12 PM

Post #6 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

Given the context and examples, I've been assuming duration especially since -- as you say -- adding times makes no sense!


adrockjames
stranger

Jul 3, 2000, 12:13 PM

Post #7 of 7 (2081 views)
Re: Array Question [In reply to] Can't Post

yeah, durations. sorry for not clarifying better.

thanks for the help!

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives