2

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 an 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* |
-----------------------------------------

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

Improve this question

5 Answers 5

Reset to default
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
    ...
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is really close to what I'm trying to do! with the exception of... I just need to be able to tell if the particular column "is_different" - this way i can just highlight just "user_name"(or any other column) is this even possible? Another thing, the number of could change at anytime, would you suggest building the query with php first or is there some way to do "IF(table2.user_name != table1.user_name, 1, 0) AS is_different" for each column dynamically with mysql? thanks so much!
If I understood you correctly; sure, you can compare any columns with the same method. The only thing that matters is that the ID's of the compared rows are equal (obviously). I edited my answer to show you how to add several tests in one query.
4 
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>
Improve this answer

Comments

0

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
Improve this answer

Comments

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

Improve this answer

Comments

0

I think the best way is 

$table1 =  mysql_query_return_array("DESCRIBE `table1`" )<br>

$table2 =  mysql_query_return_array("DESCRIBE `table2`" )<br>

if( json_encode($table1) ==  json_encode($table2)  ){
    echo "there is different";

}else{
    echo "they are same";
}
Improve this answer

1 Comment

Don't use mysql, use mysqli instead

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.