CODEWITHSUNDEEP
phpmysql
Ramesh Pareek
Ramesh Pareek

Reputation: 1669

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

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++;
        }


    }
?>

Upvotes: 0

Views: 48

Answers (2)

Jonathan Kuhn
Jonathan Kuhn

Reputation: 15301

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}....

Upvotes: 1

crashxxl
crashxxl

Reputation: 679

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

Upvotes: 0

Related Questions