tinmanbf Posted May 10, 2007 Share Posted May 10, 2007 Hi all I'm trying to do a really simple project but I can't get my head around it. What I want to do is have a really simple MySQL table, it will just have a field called Time and another part that will contain the time. The idea is to use a form on a website to enter a number (a time in full hours) and then submitting the form will update the time in the database, Counting upwards, or even down. The idea being that the database will allow up to 40 hours of time a week and then once 40 hours are up it will give a message to say no more time avaiable, and also show the time left on the form webpage. At the end of the week the time will reset, or get reset by a command and will start over again I'm sure this is real easy to do, but Im having problems getting my head around it. Can anyone help me on my way?? Thanks Ben Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/ Share on other sites More sharing options...
fenway Posted May 10, 2007 Share Posted May 10, 2007 What would this be for? I'm confused... Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250091 Share on other sites More sharing options...
tinmanbf Posted May 10, 2007 Author Share Posted May 10, 2007 I need to log time, and basically I can give 40 hours of task time a week, so I want to be able to get people to enter the amout of time they need in a task, eg lets say some one wants 2 hours, I need this deducted from the 40, because I only have 40 hours of time a week, I need to update this so the people filling out the time, know how much time I have left. so when it goes to 0 I have no more task time left until the next week when it gets reset. Can you understand, or have I confused things more. Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250099 Share on other sites More sharing options...
claudiofranck Posted May 10, 2007 Share Posted May 10, 2007 All you would need to is get current value on the language you are programming, substract the hours filed in the form, very that the number is not negative and do an update query update table set timefield = 'variable from your language'; That should do it is much easier that kind of process on the client. If you tell me what language your are using I might be able to help out more Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250192 Share on other sites More sharing options...
tinmanbf Posted May 10, 2007 Author Share Posted May 10, 2007 I'm using php, I am just starting out so haven't got very far, so far I have set up the database table and written a short script, but this doesn't do any calculations as yet and only adds more rows instead of updating one single row: <?php $db_host = "humbug"; $username = "benzen78"; $password = "------"; $DB_name = "benzen78_pn"; $chan = mysql_connect ($db_host, $username, $password); mysql_select_db ($DB_name, $chan); $sql="INSERT INTO Transfer_hours SET Time='$Time' "; mysql_query($sql); echo '<font face="arial,verdana" size=2><B>Hours have been updated!</B><P></font>'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250207 Share on other sites More sharing options...
Wildbug Posted May 10, 2007 Share Posted May 10, 2007 Here's an idea: At minimum: CREATE TABLE scheduled_time ( id INT UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL PRIMARY KEY, person_id SMALLINT UNSIGNED NOT NULL, weekdate DATETIME, hours TINYINT NOT NULL DEFAULT 0); Sample queries: -- See everything: SELECT person_id,YEARWEEK(weekdate),40-SUM(hours) FROM scheduled_time GROUP BY YEARWEEK(weekdate),person_id; -- See people with hours left this week: SELECT person_id,YEARWEEK(weekdate),40-SUM(hours) FROM scheduled_time WHERE YEARWEEK(weekdate)=xx GROUP BY person_id HAVING SUM(hours) > 0; -- Find how much time a given person has left for a given week SELECT 40-SUM(hours) FROM scheduled_time WHERE person_id=xxx AND YEARWEEK(weekdate)=xx; That may not be the best way, but it is a way. It'd probably be better to just keep everyone's current available hours in a single row for each week, when presenting the form, query the database for a row, if none exists, assume 40 hours, then check their entry and INSERT it, otherwise UPDATE. Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250212 Share on other sites More sharing options...
bubblegum.anarchy Posted May 11, 2007 Share Posted May 11, 2007 I would do something like this: DROP TABLE IF EXISTS scheduled_time; CREATE TABLE scheduled_time ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'unique idenfier reference', person_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'person.id foreign key reference', time_allowed TIME NOT NULL DEFAULT '00:00' COMMENT 'amount of time allowed', time_used TIME NOT NULL DEFAULT '00:00' COMMENT 'amout of time used', INDEX FK_PERSON_ID (person_id) ) TYPE=MyISAM; # insert new scheduled time record INSERT INTO scheduled_time (person_id, time_allowed) VALUES (1, '34:45'); # increment the time used UPDATE scheduled_time SET time_used = addtime(time_used, '02:30') WHERE scheduled_time.id = 1; # select the time remaining SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1; Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250245 Share on other sites More sharing options...
tinmanbf Posted May 11, 2007 Author Share Posted May 11, 2007 I'll look at this when I get home and have a play around. I only need to update the time of one person, so do I need a person ID? my database table is very simple: Time 40 and that's it, there is no need for any other rows because it's just the one set of 40hours that needs to be deducted. I'll try your way and see if I can get it to work, as I said I am very new to this so just need to try and get my head around it all. Thanks for your help and I'll post if I need any more help. Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250459 Share on other sites More sharing options...
tinmanbf Posted May 11, 2007 Author Share Posted May 11, 2007 Hi bubblegum.anarchy Ok this looks as if it's going to work but I'm having a problem displaying the timediff data. I want to show the time that is left on the same page as the form that is filled in, I have been able to display the whole table in the process.php scrpit below, but this isn't what I really want to do, the only data I want to see is the result from the timediff command: $sql="SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1 "; This is the code I have so far in my process.php that the form data is sent to: <?php $sql="UPDATE scheduled_time SET time_used = addtime(time_used, '$Time') WHERE scheduled_time.id = 1 "; mysql_query($sql); echo '<font face="arial,verdana" size=2><B>Transfer hours have been updated!</B><P></font>'; $query="select * from scheduled_time"; $result=mysql_query($query); echo "<table border=1>"; echo "<tr>"; for($i=0;$i<mysql_num_fields($result);$i++) { echo "<th>"; echo mysql_field_name($result, $i); echo "</th>"; } while($row=mysql_fetch_row($result)) { echo "<tr>"; for($j=0;$j<$i;$j++) { echo "<td>"; echo $row[$j]; echo "</td>"; } echo "</tr>"; } echo "</tr>"; echo "</table>"; ?> also, would there be a way to get the time_allowed field to reset back to 40:00:00 on a Sat every week? Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250732 Share on other sites More sharing options...
bubblegum.anarchy Posted May 12, 2007 Share Posted May 12, 2007 I do not see any code there that is even attempting to display the time difference. A crobjob or windows task manager can update the database at a particular time of the day, depending on platform. Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-250993 Share on other sites More sharing options...
tinmanbf Posted May 12, 2007 Author Share Posted May 12, 2007 That's the problem, I don't know how to do it, i've tried loads of things but keep on getting errors, any help in the right direction would be greatfully received. I add the following: $sql="SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1 "; and then I guess I have to call time_diff to display the results, I thought I could do something like: $query="select time_diff from scheduled_time"; $result=mysql_query($query); but I really can't get my head around how I'm to do it. I am just learning this so thanks for your time Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-251002 Share on other sites More sharing options...
bubblegum.anarchy Posted May 12, 2007 Share Posted May 12, 2007 $result = mysql_query($query = "SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1"); $record = mysql_fetch_assoc($result); print $record['time_diff']; Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-251010 Share on other sites More sharing options...
tinmanbf Posted May 12, 2007 Author Share Posted May 12, 2007 Thank you, you are a star I'll be fine from here on in Quote Link to comment https://forums.phpfreaks.com/topic/50845-solved-a-really-easy-project-but-need-help/#findComment-251015 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.