how to join two table in mysqli using php i have two table 1st is checkin and 2nd is checkout and i am trying to merge two table with condition please help me to fix this issue
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 want 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); ?> 3 Answers
Answers 1
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.
Answers 2
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)';Answers 3
1) Please check updated code <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "test"; // Create connection $con = new mysqli($servername, $username, $password, $dbname); // Check connection if ($con->connect_error){ die("Connection failed: " . $conn->connect_error); } $sql = "SELECT checkin.userid as useridn, checkin.currentdate as cdate, checkin.currenttime as intime, checkout.currenttime as outtime FROM checkin JOIN checkout ON checkin.userid = checkout.userid group by checkin.cid"; if($result = mysqli_query($con, $sql)) { echo "<table class='table table-bordered'>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Date</th>"; echo "<th>Time</th>"; echo "</tr>"; $s=1; while($row = mysqli_fetch_array($result)){ $uid = $row['useridn']; $cdate = $row['cdate']; $s1 = "select * from checkout where userid='$uid' and currentdate='$cdate' and id='$s'"; $sql2 = mysqli_query($con, $s1); $row1 = mysqli_fetch_assoc($sql2); echo "<tr>"; echo "<td>" . $row['useridn'] . "</td>"; echo "<td>" . $row['cdate'] . "</td>"; echo "<td>In :". $row['intime'] . "</td>"; echo "<td>Out :" . $row1['currenttime'] . "</td>"; echo "</tr>"; $s++;} echo "</table>"; } ?> 2) Database file:
CREATE TABLE `checkin` ( `cid` int(11) NOT NULL, `userid` int(11) DEFAULT NULL, `currentdate` date DEFAULT NULL, `currenttime` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `checkin` -- INSERT INTO `checkin` (`cid`, `userid`, `currentdate`, `currenttime`) VALUES (1, 60, '2018-03-08', '03:10:00'), (2, 60, '2018-03-08', '05:50:00'), (3, 60, '2018-03-08', '08:20:00'), (4, 20, '2018-03-08', '01:04:00'), (5, 60, '2018-03-09', '11:23:00'), (6, 20, '2018-03-09', '10:24:00'); -- -------------------------------------------------------- -- -- Table structure for table `checkout` -- CREATE TABLE `checkout` ( `id` int(11) NOT NULL, `userid` int(11) NOT NULL, `currentdate` date NOT NULL, `currenttime` time NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `checkout` -- INSERT INTO `checkout` (`id`, `userid`, `currentdate`, `currenttime`) VALUES (1, 60, '2018-03-08', '04:05:00'), (2, 60, '2018-03-08', '06:10:00'), (3, 60, '2018-03-08', '09:25:00'), (4, 20, '2018-03-08', '07:30:00'), (5, 60, '2018-03-09', '12:30:00'); 
0 comments:
Post a Comment