1

I am uploading Bio-Metric data to my MYSQL database table.I am using PHP Codeigniter. In here i have the IN-TIME and OUT-TIME of a employee. But the employee may login and go out for some work and again come back in. So what i want is to select his first login and his last logout from my database. Now i have a dump for all days of the month!

So how to fetch the first login of the employee and his last logout?

My Table is as below:Datatable Now the EMP_ID is the user-id ,date_data is the date and entry contains his in or out information.

If The entry>100 its called intime and else it in logout

This is my Table Schema:

CREATE TABLE IF NOT EXISTS `daily_data2` ( `id` int(10) NOT NULL AUTO_INCREMENT, `emp_id` int(10) NOT NULL, `date_data` varchar(30) NOT NULL, `abc` varchar(10) NOT NULL, `def` varchar(10) NOT NULL, `entry` int(10) NOT NULL, `ghi` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3720 ;

So how to filter the data and get only my first login and last logout? This is My DATA:

data

Improve this question
3
  • I want to show a month's view in which i can see for each user who logged in at what time and logged out at what time Commented Dec 16, 2015 at 7:13
  • I have created another table called users in which i have added the names of each emplyoee and have manually inserted Emp_id against each of them Commented Dec 16, 2015 at 7:15
  • can this help me: SELECT daily_data2.entry, users.emp_id, users.name FROM daily_data2, users WHERE daily_data2.emp_id = users.emp_id; Commented Dec 16, 2015 at 7:27

1 Answer 1

Reset to default
1

This function will return one record from the database per employe. the record will be the first and last in the last 24 hours. you can switch between desc and asc for the newest or oldest result. you can also store the ORDER BY as @param and call it two times. 

class YOUR_MODEL extends CI_Model { public $time = ''; /** * @param $emp_id * getting the first & last login in the last 24 hours from the database. * results are limited to one and can be ordered by ASC, or DESC */ function get_attendance_history($emp_id) { $hours = $this->time = date('Y-m-d H:i:s', strtotime('-1 day', time())); //desc will print the newest records && asc the latest. $sql = "SELECT * FROM daily_data2 WHERE emp_id = '{$emp_id}' AND date_data > '{$hours}' ORDER BY `date_data` desc LIMIT 1"; $result = $this->db->query($sql); //run the query print_r( $result->result_array() ); //debug the result //return $result->row()->emp_id; //for one record show.. exit; } }

Usage in Controller:

public function test() { $this->load->model('test_m'); $emp_id = '33'; //your emp_id $rajan = $this->test_m->get_attendance_history($emp_id); }
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

what should be my controller??
i did this: public function test($emp_id) { $this->load->model('test_m'); $rajan = $this->test_m->get_attendance_history(); }
shows me error for Message: Missing argument 1 for Test_M::get_attendance_history(), called in C:\wamp\www\trial\application\controllers\admin\attendance.php on line 51 and defined
i got a blank array Array ( )
Did you changed the number 33 to a proper number from your database? from the screen shot i can see for example the number 1
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.