[SOLVED] A Really easy project, but need help

[tinmanbf]

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

[fenway]

What would this be for? I'm confused...

[tinmanbf]

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.

 

 

[claudiofranck]

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

[tinmanbf]

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>';

 

?>

[Wildbug]

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.

[bubblegum.anarchy]

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;

[tinmanbf]

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.

 

[tinmanbf]

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?

[bubblegum.anarchy]

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.

[tinmanbf]

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

 

[bubblegum.anarchy]

$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'];

[tinmanbf]

Thank you, you are a star  I'll be fine from here on in