MySQL Query Database Attendance get in/out

Question

I have database Microsoft Access

Table CHECKTIME

USERID    CHEKTIME
1         2015-06-21 07:34:00
1         2015-06-21 07:50:00
1         2015-06-21 16:00:00
1         2015-06-21 16:48:00
2         2015-06-21 07:08:00
2         2015-06-21 13:48:00
2         2015-06-21 17:08:00
3         ....................

I want to transfer data into MySQL query with results:

userId    colDate         colIn          colOut
1         2015-06-21      07:34:00       16:48:00
2         2015-06-21      07:08:00       17:08:00
3         ..........      ........       ........

Please help me

this query makes me stuck for final test

why data can't insert into PHPMyAdmin? or ms access can't declare MIN and MAX query? How about this function and query:

public function getPresensi(){


$dbName = $_SERVER['DOCUMENT_ROOT']."/presensi-app/assets/database/upj.mdb";
$user = "";
$password = "";


$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", $user, $password);

$qry = "SELECT USERID as id, CHECKTIME as check, min(CHECKTIME) as masuk, max(CHECKTIME) as keluar FROM CHECKINOUT group by USERID,date(CHECKTIME)";

$result = odbc_exec($conn,$qry);

while ($data[] = odbc_fetch_array($result));

odbc_free_result($result);

odbc_close($conn);

$tbl = 'tb_kehadiran';
$db_mysql = $this->load->database('default',TRUE);
$db_mysql->trans_start();
$db_mysql->truncate($tbl);
foreach($data as $k => $v){

    $data_mysql = array(

        'colUserID' => $v['id'],
        'colDate' => $v['check'],
        'colJamMasuk' => $v['masuk'],
        'colJamKeluar' => $v['keluar'],

    );
    if($data_mysql != null){
    $db_mysql->insert($tbl, $data_mysql);
    }

    }

    $db_mysql->trans_complete();
    $db_mysql->close();


}

Answer 1

Building on you're comment, try this:

SELECT USERID as id, CHEKTIME as date, MIN(CHEKTIME) as colIn, MAX(CHEKTIME) as colOut FROM timetable group by userid,date(chektime)

When using max and min to get results, you'll only get one result if you're not using the grouping by option. Add the grouping by to group by user and date (so you'll also get more then one date, for each user)

you can check it out here at sqlfiddle

and to change the date format use:

date_format(CHEKTIME,'%y-%m-%d') as date

You're question change a lot since I started This is a wrap up of what problems I've seen so far with you post/code.

1. you don't declare you're mysql connection
2. you free result and close connecting to ms-access before doing anything with the data
3. you're using the deprecated mysql instead of mysqli
4. you haven't tested if you're query return results (with both MS-Access as reference or your code)

Take on step at a time and start debugging what you have/don't have.

Steps to resolve:

1. Test query in MS-Access and see if you have results, fix if no result
2. Change your code to print the result to screen before doing anything else
3. create another connection to mysql (not MS-Access)

to display all errors add the following right after opening php

 ini_set('display_errors', 1); 
 ini_set('log_errors', 1); 
 ini_set('error_log', dirname(__FILE__) . '/error_log.txt'); 
 error_reporting(E_ALL);
 $error='';

Update 2: The ms-access query should be:

SELECT Checktime.USERID, DateValue([CHEKTIME]) AS [Day], Min(TimeValue([CHEKTIME])) AS [In], Max(TimeValue([CHEKTIME])) AS Out FROM Checktime GROUP BY Checktime.USERID, DateValue([CHEKTIME]);