Sum Hours and Minutes

[aor]

Hi,

I want to record Hours and Minutes played by players in a game.

I'm currently recording two inputs from a web form. Hours go into one field and minutes into another.

Ultimately I want to display on a webpage the total amount of hours and minutes played by each player.

An example of the time recorded so far would be

Player 1 - 9 hours 185 minutes

What is the best way of doing this where the output to the webpage would be

Player 1 - 12 hours 5 minutes

thank you in advance

aor

[r937]

best way, in the long run, is to store only total minutes

display the times however you like, as hh:mm, X hours Y minutes, nnn minutes, whatever (just not 9 hrs 185 min as you have in your example)...

also, let the users enter two values, hours and minutes, as that'll be easier to input

but it will be a lot better for your sql if you store only minutes

[aidos]

If minutes is > 60, divide the minutes by 60. Add the hours part of the answer to the hours column and convert the remainder to minutes and display it.

If you have to enter hours and minutes in the db, you could write a little function in the application to do the above maths for you whenever you need it.

[aor]

thanks for the quick replys......
dont really have to store as hours and minutes i guess
thought it would be easier to input as hours and minutes
but i do need to display as hours and minutes.....
thanks again

[aidos]

thanks for the quick replys......
dont really have to store as hours and minutes i guess
thought it would be easier to input as hours and minutes
but i do need to display as hours and minutes.....
thanks again

You can still input hours and minutes in your html form if you wish, but I would recommend converting the hours and minutes to total minutes and store that in your db table. You could write a little application function to do the time conversion in both directions before storing/displaying the data.

[r937]

dont really have to store as hours and minutes i guess

good

thought it would be easier to input as hours and minutes

it is

but i do need to display as hours and minutes...

you can

[aor]

just a thought here but could i create a view based on my time table where it selects and groups the players and performs the hours/minutes mathematics.....would this be a better approach than using php ?

[aidos]

if you wanted to do the maths in php this quick and simple function will do the conversion in the appropriate direction depending on what data is passed to it.

<?php
//function accepts 1 or 2 arguments
//total minutes or hours and minutes
function convertTime() {
    $args = func_get_args();
    switch (count($args)) {
        case 1:     //total minutes was passed so output hours, minutes
            $time = array();
            $time['hours'] = floor($args[0]/60);
            $time['mins'] = ($args[0]%60);
            return $time;
            break;
        case 2:     //hours, minutes was passed so output total minutes
            return ($args[0] * 60) + $args[1];
    }
}
 
//test the function
$hours = 3;
$mins = 35;
echo 'total minutes = '.convertTime($hours,$mins);
 
$totalMinutes = 182;
$times = convertTime($totalMinutes);
echo '<br /><br />Hours = '.$times['hours'].'<br />Minutes = '.$times['mins'];
 
?>

[aor]

Thanks again Aidos

My head is a bit melted over this now...I have a piece of php that handles input but I'm not sure how to modify it to work with your code above....

My input page has a form which the user can dynamically add fields but starts out with at least one record for player

Name:
        <select name="player[1][userid]" id="player[1][userid]" class="playerInput">
        	<option value=""></option>
........
<select name="player[1][hours]" id="player[1][hours]">
.........
<select name="player[1][minutes]" id="player[1][minutes]">

This produces the following query string when the form is submitted (for 3 players)

http://........&player[1][userid]=1&player[1][hours]=2&player[1][minutes]=15&player[2][userid]=7&player[2][hours]=3&player[2][minutes]=30&player[3][userid]=11&player[3][hours]=1&player[3][minutes]=45

I use the following php to loop through this query string and insert into database (for each player I insert their Id, Hours, Minutes)

$sql = 'Insert into '.$tbl_name.' (date,playerid,hours,mins) values ';
	foreach ($_GET as $key => $value) {
		if($key != 'submit'){
			if(is_array($value)) {	
				foreach ($value as $iKey => $iValue) {
					$sql = $sql . '('.$date.',';
					foreach ($iValue as $xKey => $xValue) {
						$sql = $sql.$xValue;
						if ( $xValue !== end($iValue))
							$sql = $sql.',';
					}
					$sql = $sql.')';
					if ( $iValue !== end($value))
							$sql = $sql.',';
				}
			}
 
		}
	}

Apologies if this has drifted off the original question or is in the wrong forum.

I'm not sure how I can convert this on the way into the database using the above foreach loops.

At the moment my table has id, date, playerid, hours, mins but I can combine hours and mins, as I said previously to mins.

thanks again for all your help....

[aidos]

Unfortunately your code looks a little messy (no offence) so let me describe what I would do.

You can store the time in the db table in either hours and minutes or just minutes. My preference would be just minutes but that is your call. But however you store the time in the db table, you can still input the time in hours and minutes in your html form, convert them to total minutes just before inserting in the db and then convert retrieved total minutes back to hours, minutes for display purposes on a web page.

Let's assume the user will be inputting hours, minutes which will be stored as total minutes.

<?php
 
$hours = $_POST['hours'];
$mins = $_POST['mins'];
 
/*
Do your data validation to make sure $hours, $mins are integers
*/
 
$sql = 'insert into tableName (userId,minutes) '.
         'values ("'.$userId.'","'.convertTime($hours,$mins).'")';
 
//then run the query to do the insert
 
?>

and to retrieve the total minutes and display as hours, minutes

<?php
 
$sql = 'select userId, minutes from tableName where userId = "'.$userId.'"';
$rs = mysql_query($sql,$conn);
 
$row = mysql_fetch_assoc($rs);
 
//output the retrieved total minutes as hours, minutes
 
$times = convertTime($row['minutes']);
 
echo '<br /><br />Hours = '.$times['hours'].'<br />Minutes = '.$times['mins'];
 
?>

Hopefully you will be able to use the above concepts to adjust your code.

[aor]

No offence taken Aidos....esp after all the work you've put in helping me!...my background is more html/css so I'm struggling a bit with these array values....

I have definitely drifted off the original question here....Is it ok to pm you on how i would loop through the query string input to get the individual player inputs for multiple players ?

[aidos]

It's probably better to keep posting here. I'll have to go shortly and in the mean time someone else might be able to help you or I can try to help more later on.

Also, there might be others viewing this thread who might find this discussion helpful.

[aor]

No problem.....its exactly the kind of thread i wish i had found 3 or days ago!

I'll try to loop differently through the query string array values

thank you once again...and r937

[aidos]

I'm making some assumptions here to hopefully simplify your code.

1. there is a select list for each userId in the html form

2. each userId select will have a corresponding hours and minutes select.

3. the hours, minutes from the selects will be converted to total minutes for storing in the db table.

4. in trying to keep to the KISS principle as much as possible, I haven't included any error checking/data validation code.

Rather than have individual names for each of the select lists in the html form, I have given each of the 3 selects the same name for their data type. This will result in an array of values being sent to the server for the userId, hours and minutes. Using arrays will make looping the db inserts a lot easier.

The selects in the html form could be similar to this

<select name="selUserId">
...
<select name="selHours">
...
<select name="selMins">
...
 
<select name="selUserId">
...
<select name="selHours">
...
<select name="selMins">
...

and the php to process the form data could be something like this

<?php
//transfer the form data to arrays $users, $hours, $mins
$users = $_POST['selUserId'];
$hours = $_POST['selHours'];
$mins  = $_POST['selMins'];
 
//array to store the insert query for each user
$query = array();  
 
//build the insert queries
foreach($users as $i => $userId) {
    $query[] = 'insert into tableName (userId,minutes) '.
    'values ("'.$users[$i].'","'.convertTime($hours[$i],$mins[$i]).'")';  
}
 
//now run the insert queries
foreach($query as $sql) {
    if(!mysql_query($sql,$conn)) {
        echo '** Error - could not insert record'; die();
    }
}
 
?>

[aidos]

oops

<select name="selUserId">

should be

<select name="selUserId[]">

The other select names need the [] as well.

[aor]

thats great work aidos......much appreciated....

i havent been at my computer all day but i came up with something last night which seems to work so far (i cant believe it either!)....using my existing loops to capture the user inputs (one insert query)....

the userid, hours and minutes are input into database as before....

$sql = 'Insert into '.$tbl_name.' (date,playerid,hours,mins) values ';
	foreach ($_GET as $key => $value) {
		if($key != 'submit'){
			if(is_array($value)) {	
				foreach ($value as $iKey => $iValue) {
					$sql = $sql . '('.$date.',';
					foreach ($iValue as $xKey => $xValue) {
						$sql = $sql.$xValue;
						if ( $xValue !== end($iValue))
							$sql = $sql.',';
					}
					$sql = $sql.')';
					if ( $iValue !== end($value))
							$sql = $sql.',';
				}
			}
 
		}
	}
 
	mysql_select_db($db, $conn);
        if (mysql_query($sql,$conn))
		echo "<br/>Time Saved in Database";
	else
		die ('There was a problems entering the time');

When displaying the results i then run the following query...

$result = mysql_query("select tbl_playerDetails.name,  sum(tbl_cashLeague.hours) as Total_Hours, 	sum(tbl_cashLeague.mins) as Total_Mins
					from tbl_playerDetails
					inner join tbl_cashLeague
					on tbl_playerDetails.userid = tbl_cashLeague.playerid
					group by name
					order by Total_Hours desc") or die ('Error: '.mysql_error ());

Then when i'm outputting the results I use the part from your convert function (which i will incorporate)

echo "<table align='left'>";
	echo "<tr><th>Position</th><th>Player Name</th><th>Hours</th><th>Minutes</th></tr>";
 
	$pl = 0;
	$pos = 0;
	$h = 0;
	$m = 0;
 
	while($row = mysql_fetch_array($result)) {
 		$pos +=1; 
		if( $row[2] > 59) {
			$h = Floor($row[2] / 60); 
       		        $m = ($row[2] - ($h * 60)); 
			$row[1] = $row[1] + $h;
			$row[2] = $m;
		}
		echo '<tr><td>'.$pos.'</td><td>'.stripslashes($row[0]).'</td><td>'.$row[1].'</td><td>'.$row[2].'</td></tr>';
	}
 
	echo '</table>';

I'm gonna give ur method a go too.....
thanks again...

[aidos]

you're welcome