SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sum Hours and Minutes

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aor View Post
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aor View Post
    dont really have to store as hours and minutes i guess
    good

    Quote Originally Posted by aor View Post
    thought it would be easier to input as hours and minutes
    it is

    Quote Originally Posted by aor View Post
    but i do need to display as hours and minutes...
    you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  8. #8
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code PHP:
    <?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'];
     
    ?>

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code HTML4Strict:
    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)

    Code PHP:
    $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....

  10. #10
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code PHP:
    <?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

    Code PHP:
    <?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.

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  12. #12
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code HTML4Strict:
    <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

    Code PHP:
    <?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();
        }
    }
     
    ?>

  15. #15
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops

    Code HTML4Strict:
    <select name="selUserId">

    should be

    Code HTML4Strict:
    <select name="selUserId[]">

    The other select names need the [] as well.

  16. #16
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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....
    Code PHP:
    $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...

    Code PHP:
    $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)

    Code PHP:
    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...

  17. #17
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're welcome


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •