How to join two table in mysqli using php

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'); 

Read More

How to get record which have min has_many rec ords(joins data)

user.rb

has_many :properties 

property.rb

belongs_to :user 

I want to get a user who have min properties like wise for max also.

I cant find any query related to that

3 Answers

Answers 1

To find the user with min properties you can simply do,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").last 

And to find the user with max properties,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").first 

Note: Because its a join operation with properties, so user with no properties will not appear in this query.

Answers 2

You could use counter_cache.

The :counter_cache option can be used to make finding the number of belonging objects more efficient.

From here

belongs_to :user, counter_cache: true 

Then create the migration:

def self.up   add_column :users, :properties_count, :integer, :default => 0    User.reset_column_information   User.find(:all).each do |u|     User.update_counters u.id, :properties_count => u.properties.length   end end 

Then you can fetch user which have max properties_count

User.maximum("properties_count") 

Here is an awesome RailsCast about counter_cache

Answers 3

I think you can do like this by scopes

class User   has_many :properties   scope :max_properties,     select("users.id, count(properties.id) AS properties_count").     joins(:properties).     group("properties.id").     order("properties_count DESC").     limit(1)    scope :min_properties,     select("users.id, count(properties.id) AS properties_count").     joins(:properties).     group("properties.id").     order("properties_count ASC").     limit(1) 

And just call User.max_properties and User.min_properties

UPDATED:

It will aslo work like BoraMa suggeted

class User   has_many :properties   scope :max_properties,     select("users.*, count(properties.id) AS properties_count").     joins(:properties).     group("users.id").     order("properties_count DESC").     limit(1)    scope :min_properties,     select("users.*, count(properties.id) AS properties_count").     joins(:properties).     group("users.id").     order("properties_count ASC").     limit(1) 

Read More

find all that are nil in the association in ruby on rails

Here I've got a 1-to-many relationship between Products and Users:

class Property < ActiveRecord::Base   has_many :users end  class User < ActiveRecord::Base   belongs_to :property end 

How could I get all the properties which do not belong to any user?

6 Answers

Answers 1

To get all properties that have no user, try this:

Property.includes(:users).where(users: { property_id: nil }) 

Answers 2

You can try this query:

Property.where.not(:id=>User.where.not(:property_id=>nil).pluck(:property_id)) 

or

 Property.where.not(:id=>User.where.not(:property_id=>nil).pluck("DISTINCT property_id")) 

Answers 3

One more approach would be to write some SQL:

Property.joins("LEFT OUTER JOIN users ON users.property_id = properties.id"). where('users.id IS NULL'). uniq 

The code above is being translated to the following pure SQL query to the database:

SELECT DISTINCT properties.* FROM properties  LEFT OUTER JOIN users on users.property_id = properties.id  WHERE users.id IS NULL; 

LEFT JOIN keyword returns all rows from the left table (properties), with the matching rows in the right table (users). The result is NULL in the right side when there is no match. Afterwards WHERE keyword filters results by a condition that we're intrested in those rows which have NULL on the right side only.

Reference: SQL LEFT JOIN Keyword

Answers 4

You can do it like this too:

Property.where('id NOT IN (SELECT DISTINCT(property_id) FROM users)') 

Another option would be:

Property.where("(select count(*) from users where property_id = properties.id) = 0") 

You can always check which is more efficient according to you application by checking the time take to execute the queries and choose an option accordingly.

Answers 5

use this code:

@users= User.includes(:properties).where(properties: { property_id: nil }) 

Answers 6

Also You can write scope based on this query just for easy use.

class Property < ActiveRecord::Base   has_many :users   scope :incomplete, -> { joins(:users).where("property.user_id is null") } end 

Then, You can call this scope like this: Property.incomplete

Read More

Pandas join without replacement

This is a bit hard to explain but I'm going to try my best. What I've got right now is two tables I need to join together, but we don't really have a unique join id. I have a couple columns to join on that is the best I can do, and I just want to know when we don't have equal numbers on both sides of the joins. Right now, if the right table has 1 match to the 2 entries on the left table, that 1 match joins to both entries. That leaves me not knowing the right table only has 1 entry vs the 2 for the left.

What I want is to join a right table to a left (outer), but I don't want to join the right table more than once per entry. So if the right table index 3 could be joined on index 1 and 2 on the left, I only want it to be joined on index 1. Also, if index 3 and index 4 could be joined on index 1 and 2, I want index 1 to be matched with index 3, and index 2 to be matched with index 4. If there is only 1 match (Index 1 --> 3), but Index 2 on the left table could be matched to index 3, I want Index 2 to not be joined.

Examples may best describe this:

a_df = pd.DataFrame.from_dict({1: {'match_id': 2, 'uniq_id': 1}, 2: {'match_id': 2, 'uniq_id': 2}}, orient='index')  In [99]: a_df Out[99]:    match_id  uniq_id 1         2        1 2         2        2   In [100]: b_df = pd.DataFrame.from_dict({3: {'match_id': 2, 'uniq_id': 3}, 4: {'match_id': 2, 'uniq_id': 4}}, orient='index')  In [101]: b_df Out[101]:    match_id  uniq_id 3         2        3 4         2        4 

In this example, I want a_df to join onto b_df. I want b_df uniq_id 3 to be matched with a_df uniq_id 1, and b_df 4 to a_df 2.

Output would look like this:

Out[106]:    match_id_right  match_id  uniq_id  uniq_id_right 1               2         2        1              3 2               2         2        2              4 

Now let's say we want to join a_df to c_df:

In [104]: c_df = pd.DataFrame.from_dict({3: {'match_id': 2, 'uniq_id': 3}, 4: {'match_id': 3, 'uniq_id': 4}}, orient='index')  In [105]: c_df Out[105]:    match_id  uniq_id 3         2        3 4         3        4 

In this case, we have match_ids of 2 on a_df, and only 1 match_id of 2 on c_df.

In this case I just want uniq_id 1 to be matched with uniq_id 3, leaving both uniq_id 2 and uniq_id 4 to be unmatched

   match_id_right  match_id  uniq_id  uniq_id_right 1               2         2        1              3 2             NaN         2        2            NaN 4               3       NaN      NaN              4 

1 Answers

Answers 1

Alright guys, so the answer is actually pretty simple.

What you need to do is group each dataframe (left, right) by the matching column(s), and then add a new counter column for each group.

Now you do the outer join and include the counter column, so you'll match on 0,1, but if the right has a 2 then it doesn't match. If the left only has 0, it will match the right but if the right has 0,1, then the right '1' entry doesn't match!

Edit: Code request.

I don't have anything handy, but it is very simple. If you have, say, 2 columns you're matching on ['amount','date'], then you simply do a

left_df['Helper'] = left_df.groupby(['amount','date']).cumcount() right_df['RHelper'] = right_df.groupby(['amount','date']).cumcount() 

Then use the Helper column in the join.

Read More