How to join two tables in MySQL using PHP and mysqli

Question

How to join two tables in MySQL using PHP and mysqli?

I have two tables: checkin and checkout. I am trying to merge the two tables with a condition.

Here is my table structure:

checkin

userid currentdate currenttime
60     08-03-2018   03:10
60     08-03-2018   05:50
60     08-03-2018   08:20
20     08-03-2018   01:04
60     09-03-2018   11:23
20     09-03-2018   10:24

checkout

userid currentdate currenttime
60     08-03-2018   04:05
60     08-03-2018   06:10
60     08-03-2018   09:25
20     08-03-2018   07:30
60     09-03-2018   12:30

I would like to have a result like this:

Result

Userid Date        Time
60     08-03-2018   In:03:10  Out:04:05
                    In:05:50  Out:06:10
                    In:08:20  Out:09:25
20     08-03-2018   In:01:04  Out:07:30
60     09-03-2018   In:11:23  Out:12:30
20     09-03-2018   In:10:24

Here is the PHP code:

<?php
    include 'db.php';

    $sql = '
        SELECT 
            checkin.iduser as iduser,
            checkin.currentdate as currentdate,
            checkin.currenttime as currenttime,
            checkout.iduser as iduser2,
            checkout.currentdate as currentdate2,
            checkout.currenttime as currenttime2
        FROM checkin
        LEFT JOIN checkout ON checkin.iduser = checkout.iduser
    ';

    if($result = mysqli_query($con, $sql)) {
        if(mysqli_num_rows($result) > 0) {
            echo "  <table class=\"table table-bordered\">";
            echo "<tr>";
            echo "<th>ID</th>";
            echo "<th>Date</th>";
            echo "<th>Time</th>";
            echo "</tr>";

            while($row = mysqli_fetch_array($result)) {
                echo "<tr>";
                echo "<td>" . $row['iduser'] . "</td>";
                echo "<td>" . $row['currentdate'] . "</td>";
                echo "<td>In :"
                   . $row['currenttime'] 
                   . " <br> Out:" . $row['currenttime2']
                   . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            mysqli_free_result($result);
        } else {
            echo "No records matching your query were found.";
        }
    } else {
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($con);
    }
    mysqli_close($con);
?>

Short advise: Make a split between the SQL part and PHP. You can run SQL statements using the MySQL commandline client (often called `mysql`). Do that first to find out the correct SQL syntax and then worry about how to get that into PHP. — Ulrich Eckhardt, Mar 08 '18 at 09:04

score 8 · Answer 1 · answered Mar 11 '18 at 13:06

Assuming there is no checkout without a corresponding checkin on the same day. (For example: checkin at 23:30 and checkout at 00:30 the next day is not possible.) Then you can select the times from the checkin table and get the corresponding checkout time in a correlated subquery.

select 
  i.userid,
  i.currentdate,
  i.currenttime as checkin,
  (
    select min(o.currenttime)
    from checkout o
    where o.userid = i.userid
      and o.currentdate = i.currentdate
      and o.currenttime > i.currenttime
  ) as checkout
from checkin i

The result would be like this

| userid | currentdate | checkin | checkout |
|--------|-------------|---------|----------|
|     60 |  08-03-2018 |   03:10 |    04:05 |
|     60 |  08-03-2018 |   05:50 |    06:10 |
|     60 |  08-03-2018 |   08:20 |    09:25 |
|     20 |  08-03-2018 |   01:04 |    07:30 |
|     60 |  09-03-2018 |   11:23 |    12:30 |
|     20 |  09-03-2018 |   10:24 |   (null) |

Demo: http://sqlfiddle.com/#!9/b43a46/5

You can sort the result by date, user and checkin time

order by i.currentdate, i.userid, i.currenttime

If your currentdate is in the format DD-MM-YYYY you will need to convert it to a sortable format:

order by str_to_date(i.currentdate, '%d-%m-%Y'), i.userid, i.currenttime

You can also sort the users by the first checkin in the day with another subquery:

order by i.currentdate, (
    SELECT MIN(currenttime)
    FROM checkin i2
    WHERE i2.userid = i.userid
      AND i2.currentdate = i.currentdate
  ),
  i.currenttime

Now it's up to you to render a HTML table in PHP.

score 0 · Answer 2 · edited Mar 18 '18 at 20:45

Edit your $sql replace it with this:

$sql = '
    SELECT
        checkin.iduser as iduser,
        checkin.currentdate as currentdate,
        checkin.currenttime as currenttime,
        checkout.iduser as iduser2,
        checkout.currentdate as currentdate2,
        checkout.currenttime as currenttime2 
    FROM checkin
    LEFT JOIN checkout ON (checkin.iduser = checkout.iduser)
';

How to join two tables in MySQL using PHP and mysqli

2 Answers2