Comparing data between two MySQL tables with PHP

Question

Hi,

I have two tables table1 = records, table2 = duplicates. Both tables contain a variable number of columns at any given time and they both contain the exact same columns with the exception of table2 having and additional "ID" column... Both tables contain a column, "user_id". The data comes from a CSV import. If the "user_id" already exists in table1 it is inserted into table2.

With table2 I need to be able to grab all the rows and print them out in a table, no problem. The part I'm having a hard time figuring out... For each column in table2 that gets printed I need to check if the data matches(based on the "user_id") the data in table1 and somehow flag it... (maybe a different color background on the table cell)

Example:

table1 contains the row:

user_id | user_name
-------------------
2342342 | some name

and table2:

user_id | user_name
-------------------
2342342 | different name

then the output would be:

-----------------------------------------
|2342342 | *flag* different name *flag* |
-----------------------------------------

I'm sure I just confused you... If not... Any idea as to how I could make this work? If it helps any, I'm building this app with Codeigniter.

Thanks so much!

Answer 1

I think it's best that you use an sql query for this. For example:

SELECT
 table2.*,
CASE WHEN table2.user_name = table1.user_name THEN 'No flag' ELSE 'Flag' END CASE AS FLAG
FROM
table2
LEFT JOIN table1
ON table1.user_name = table2.user_name

Answer 2

select b.user_id, b.user_name, b.something, b.something2,
a.user_id as a_user_id, a.user_name as a_user_name,
a.something as a_something, a.something2 as a_something2
from b left join a on a.user_id = b.user_id

then your script can check all the fields you want to compare and optionally print the 2 values side by side.

you can print each record returned, and under the mismatches, print a second row containing the original values for easy comparison:

<style type="text/css">
tr.good td {background: #fff; color: #333;}
tr.bad td {background: #aaa; color: #fff;}
tr.compare td {background: #000; color: #fff;}
td.match {}
td.mismatch {background: #ff0000; color: #fff;}
</style>

<table>
<tr><th>user id</th><th>name</th><th>something</th><th>something2</th></tr>

<?php
  function tdClass($k, $matches) {
    return isset($matches[$k]) && !$matches[$k] ? 'mismatch' : 'match';
  }

  foreach ($rows as $r) {
    $good = true;
    $matches = array();
    foreach ($r as $k => $v) {
      $matches[$k] = isset($r["a_$k"]) && $r["a_$k"] === $v;
      if (!$matches[$k]) $good = false;
    }
    $url = "user/$row[user_id]";
?>

<tr class="<?php echo $good ? 'good' : 'bad' ?>">

<td><?php echo $row['user_id'] ?></td>

<td class="<?php echo tdClass('user_name', $matches) ?>">
  <a href="$url"><?php echo htmlentities($row['user_name']) ?></a></td>

<td class="<?php echo tdClass('something', $matches) ?>">
  <?php echo htmlentities($row['something']) ?></td>

<td class="<?php echo tdClass('something2', $matches) ?>">
  <?php echo htmlentities($row['something2']) ?></td>

</tr>

<?php if (!$good) { ?>

<tr class="compare">
<td></td>
<td><a href="$url"><?php echo htmlentities($row['a_user_name']) ?></a></td>
<td><?php echo htmlentities($row['a_something']) ?></td>
<td><?php echo htmlentities($row['a_something2']) ?></td>
</tr>

<?php } ?>

</tr>
</table>

Answer 3

SELECT table2.user_name FROM table2 LEFT JOIN table1 ON table1.user_id = table2.user_id WHERE table1.user_name != table2.user_name

Use this simple join. PHP shouldn't bother with data selecting and ordering, your RDBMS do the job.

Answer 4

This query will select all the entries from table2, and if the name is different than the name in table1, is_different will be 1, otherwise it's 0:

SELECT
    table2.user_name,
    IF(table2.user_name != table1.user_name, 1, 0) AS is_different
FROM
    table2
LEFT JOIN
    table1
ON
    table1.user_id = table2.user_id

EDIT

You can do several of those tests in one query, if you need to compare more than one column:

SELECT
    table2.user_name,
    table2.user_email,
    IF(table2.user_name != table1.user_name, 1, 0) AS is_name_different,
    IF(table2.user_email != table1.user_email, 1, 0) AS is_email_different
FROM
    ...