Matching two tables with duplicate entries in both tables using PHP and MySql

Question

I have two tables with four columns each. Both may contain duplicate rows. I want to store matched entries in one table, while unmatched entries in another table. Here is the code I am trying, But not getting the desired output.

<?php
    $con=mysqli_connect("localhost","root","","truck");
    // Check connection
    if (mysqli_connect_errno()){
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $sqlA="select date, truckno, bagscount from vendor";
    $sqlB="select date, truckno, bagscount from truck";
    $resultA=mysqli_query($con,$sqlA);
    $resultB=mysqli_query($con,$sqlB);
    $objA= mysqli_fetch_all($resultA,MYSQLI_NUM);
    $objB= mysqli_fetch_all($resultB,MYSQLI_NUM);
    $i=0;
    $j=0;
    while ($i<=(mysqli_num_rows($resultA)-1)){
        if ($objA[$i][0]=$objB[$j][0] && $objA[$i][1]=$objB[$j][1] && $objA[$i][2]=$objB[$j][2]){
            $i++;
            echo "row ". $i. " matches". $j. "<hr>";
            $j=0;
        }
        else 
        {
            echo "not matched!";
            $j++;
        }


    }
?>

@Maximus2012 can you please suggest an alternate method to achieve the same result. I have to separate the unmatching rows out and write them to another table — Ramesh Pareek
– Ramesh Pareek, Commented Jul 15, 2015 at 17:54

Jonathan Kuhn · Accepted Answer · 2015-07-15 18:04:42Z

Finding the matched rows:

SELECT
    `vendor`.`date`,
    `vendor`.`truckno`,
    `vendor`.`bagscount`
FROM `vendor`
    INNER JOIN `truck`
        ON `vendor`.`date`=`truck`.`date`
            AND `vendor`.`truckno`=`truck`.`truckno`
            AND `vendor`.`bagscount`=`truck`.`bagscount`

Finding the rows in vendor that aren't in truck

SELECT
    `vendor`.`date`,
    `vendor`.`truckno`,
    `vendor`.`bagscount`
FROM `vendor`
    LEFT JOIN `truck`
        ON `vendor`.`date`=`truck`.`date`
            AND `vendor`.`truckno`=`truck`.`truckno`
            AND `vendor`.`bagscount`=`truck`.`bagscount`
WHERE
    `truck`.`date` IS NULL

Finding the rows in truck that aren't in vendor

SELECT
    `truck`.`date`,
    `truck`.`truckno`,
    `truck`.`bagscount`
FROM `truck`
    LEFT JOIN `vendor`
        ON `truck`.`date`=`vendor`.`date`
            AND `truck`.`truckno`=`vendor`.`truckno`
            AND `truck`.`bagscount`=`vendor`.`bagscount`
WHERE
    `vendor`.`date` IS NULL

The left joins (last two) make one assumption that the date is never null.

You can also do an insert to another table by just preceding the select with INSERT INTO table_name SELECT ...{rest of select query}....

kittykittybangbang · Accepted Answer · 2015-07-15 18:02:49Z

0

Use JOIN in SQL query.

SELECT *
FROM vendor V
JOIN trucks T ON
V.key = T.key AND V.key2 = T.key2 ....

http://www.w3schools.com/sql/sql_join.asp

edited Jul 15, 2015 at 18:02

kittykittybangbang

2,4184 gold badges18 silver badges27 bronze badges

answered Jul 15, 2015 at 18:01

crashxxl

6794 silver badges18 bronze badges

Collectives™ on Stack Overflow

Matching two tables with duplicate entries in both tables using PHP and MySql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related