Optimizing database

[elbes]

hi all;
i have the following sample of data stored in db,the time b4 12:00:00 pm is considered as check in time and the one after 12:00:00 pm is check out.

id name date timein timeout
100000 ahmad Qudah 2007-05-01 07:30:00 00:00:00
100000 ahmad Qudah 2007-05-01 07:31:00 00:00:00
100000 ahmad Qudah 2007-05-02 02:15:00 00:00:00
100000 ahmad Qudah 2007-05-02 04:15:00 00:00:00
100000 ahmad Qudah 2007-05-02 07:15:00 00:00:00
100000 ahmad Qudah 2007-05-02 07:17:00 00:00:00
100000 ahmad Qudah 2007-05-02 00:00:00 19:15:00
100000 ahmad Qudah 2007-05-03 07:30:00 00:00:00
100000 ahmad Qudah 2007-05-03 00:00:00 19:16:00
656565 elbes 2007-05-03 08:30:00 00:00:00
656565 elbes 2007-05-03 00:00:00 17:16:00
500101 rabee 2007-05-03 09:30:00 00:00:00
500101 rabee 2007-05-03 10:16:00 00:00:00
500101 rabee 2007-05-03 11:30:00 00:00:00
500101 rabee 2007-05-03 00:00:00 13:16:00
500101 rabee 2007-05-03 00:00:00 17:16:00

if an employee has one check in and one check out
how can i update his row,for example:
i want this :
656565 elbes 2007-05-03 08:30:00 00:00:00
656565 elbes 2007-05-03 00:00:00 17:16:00

to become this
656565 elbes 2007-05-03 08:30:00 17:16:00

how can this be done plz?

[stephan.gerlach]

What you probably wanna do is insert a new row when a person checks in and then simply update the timeout field when he checks out.

[elbes]

I dont think this is possible bcoz i recieve the data in the following format in a text file.
Abdeljawad Mahmoud H,20070501,72838,919600,
Abdeljawad Mahmoud H,20070502,23044,919600,
Abdeljawad Mahmoud H,20070502,71512,919600,
Abdeljawad Mahmoud H,20070502,161510,919600,
ahmad Qudah,20070503,73000,100000,
ahmad Qudah,20070503,161500,100000,
ahmad Qudah,20070501,73000,100000,
ahmad Qudah,20070502,21500,100000,
ahmad Qudah,20070502,71500,100000,
ahmad Qudah,20070502,191500,100000,
rabee,20070502,73000,110000,
rabee,20070502,161500,110000,
rabee,20070501,72900,110000,
rabee,20070502,33044,110000,
rabee,20070502,71500,110000,
rabee,20070502,161500,110000,
oglah,20070502,93000,115500,
oglah,20070502,171500,115500,
saleem,20070501,82900,113200,
majed,20070502,73044,111000,
rabee,20070503,11500,110000,
rabee,20070503,161500,110000,
bess,20070503,71500,110000,
bess,20070503,101500,110000,
bess,20070503,161500,110000,


then i have to insert it into the database to do the optimization and calculations on time.

do u have any ideas?

[elbes]

no answers yet?

[dudhi]

what about these steps( using mysql to update the database)
1. inserting that data into a table in the database.
2. updating that table by selecting the time in but replacing 00000 with the timeout of the employee
so lets assume you get this from the text file
a. name date clockin 00000
b. name date 0000 clockout

so two rows having the same date and same employee update the first one 0000
with the clockout time of the second row.

3. deleting the table with all the timeout of the employees.
In this step delete the b row from the table.

[elbes]

I know i have to do this,i tried to do ,but it failed many times..can u plz show me how can this be done?

[dudhi]

Its a lot easier if I can see ur code. Post ur code and may be I can help you out.