0

How to find out missing date in MySQL using PHP?

echo "<tr> <th>Username</th><th>Date</th><th>Check In</th><th>Check Out</th> </tr>";

// get results1 from database 
$result1 = mysql_query("SELECT * FROM attend WHERE user_name='ali' AND date BETWEEN '2015-07-01' AND '2015-07-15' order by date");

while($row = mysql_fetch_array($result1))
{   
    // echo out the contents of each row into a table
    echo "<tr>";
    echo '<td>' . $row['user_name'] . '</td>';
    echo '<td>' . $row['date'] . '</td>';
    echo '<td>' . $row['checkin'] . '</td>';
    echo '<td>' . $row['checkout'] . '</td>';
    echo "</tr>";    
}
echo "</table>";

currently result

Username    Date    Check In    Check Out
ali     2015-07-01  11:30:34    17:23:47
ali     2015-07-02  10:11:34    17:15:15
ali     2015-07-03  09:32:34    18:16:27
ali     2015-07-06  10:41:34    16:56:13
ali     2015-07-07  08:51:34    17:36:01
ali     2015-07-08  05:61:34    17:16:26
ali     2015-07-09  04:11:34    17:14:12
ali     2015-07-10  02:81:34    17:25:25
ali     2015-07-13  11:71:34    17:02:29
ali     2015-07-14  10:81:34    17:04:20
ali     2015-07-15  09:31:34    17:00:43

and i want result like this

Username    Date    Check In    Check Out
ali     2015-07-01  11:30:34    17:23:47
ali     2015-07-02  10:11:34    17:15:15
ali     2015-07-03  09:32:34    18:16:27
        2015-07-04
        2015-07-05
ali     2015-07-06  10:41:34    16:56:13
ali     2015-07-07  08:51:34    17:36:01
ali     2015-07-08  05:61:34    17:16:26
ali     2015-07-09  04:11:34    17:14:12
ali     2015-07-10  02:81:34    17:25:25
        2015-07-11
        2015-07-12
Ali     2015-07-13  11:71:34    17:02:29
ali     2015-07-14  10:81:34    17:04:20
ali     2015-07-15  09:31:34    17:00:43
Sam FarajpourGhamari
  • 14,601
  • 4
  • 52
  • 56
user2260431
  • 75
  • 1
  • 3
  • 14
  • 3
    If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Aug 04 '15 at 13:13

4 Answers4

0

All of your missing check ins are not assigned to any user. If you want to fetch records assigned to user ali or unassigned, between gives dates:

SELECT * FROM attend WHERE (user_name='ali' OR user_name = '') AND date BETWEEN '2015-07-01' AND '2015-07-15' order by date

It would be much cleaner if you show us your schema.

Przemysław Jan Wróbel
  • 284
  • 2
  • 8
0

You can do this with a range in a function, this will permit you to reuse your code:

function getAttendInInterval($begin,$end) {

    $begin = new DateTime( $begin );
    $end = new DateTime( $end );

    $interval = DateInterval::createFromDateString('1 day');
    $period = new DatePeriod($begin, $interval, $end);

    foreach ( $period as $dt ) {
        $result = mysql_query('SELECT * FROM attend WHERE user_name="ali" AND date = '".$dt->format( 'Y-m-d' )."' order by date');

      ...
}

This fonction permits to show the attend for each day in an interval.

and then:

 getAttendInInterval('2015-07-01','2015-07-15');
Sylvain Martin
  • 2,365
  • 3
  • 14
  • 29
  • *Maybe*? Why should the OP try this? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Aug 04 '15 at 13:26
0

I'd use timestamp for that date column. If you do this, you can simply "check" which day is expected to come next. If its not that day, print that day like you wanted and then continue, if you got that expected date.

Do do so, you should save all your timestamps at a given time of that day. Like always 12:00:00. This makes handling those numbers much easier.

echo "<tr> <th>Username</th><th>Date</th><th>Check In</th><th>Check Out</th> </tr>";

// get results1 from database
$iLastTimestamp = 0 
$result1 = mysql_query("SELECT * FROM attend WHERE user_name='ali' AND date BETWEEN '2015-07-01' AND '2015-07-15' order by date");

while($row = mysql_fetch_array($result1))
{   
    if ($iLastTimestamp != 0)
    {
        while ($iLastTimestamp != $row['date'])
        {
            // echo out the contents of each row into a table
            echo "<tr>";
            echo '<td>&nbsp;</td>';
            echo '<td>' . date("Y-m-d", $iLastTimestamp) . '</td>';
            echo '<td>&nbsp;</td>';
            echo '<td>&nbsp;</td>';
            echo "</tr>";    
            $iLastTimestamp = strtotime("+1 Day 12:00:00", $iLastTimestamp)
        }
    }
    // echo out the contents of each row into a table
    echo "<tr>";
    echo '<td>' . $row['user_name'] . '</td>';
    echo '<td>' . $row['date'] . '</td>';
    echo '<td>' . $row['checkin'] . '</td>';
    echo '<td>' . $row['checkout'] . '</td>';
    echo "</tr>";    
}
echo "</table>";

Another way is to convert your date into an timestamp first, before using the code I posted. You can use the "strtotime"-Func here, too. Just add the time, just to be sure. :)

SophieXLove64
  • 316
  • 1
  • 11
-2

you can use date in LOOP and if date is match in database then show all info. of database with date otherwise show only date.

Inspector Squirrel
  • 2,548
  • 2
  • 27
  • 38
Bhawani Singh
  • 1
  • 1