I am needing some help with comparing two arrays with array_diff. I need to find the unique data one one array when compared to another. For example array1 has 200 records and array2 has 250 records, I need to be able to find the difference between the two (the 50 left over when the two are compared). Here is the code I am using.

mysql_select_db($database_name, $queryOne);
$query_rsOld = "SELECT data FROM tableOne";
$rsOld = mysql_query($query_rsOld, $queryOne) or die(mysql_error());
$row_rsOld = mysql_fetch_assoc($rsOld);
$totalRows_rsOld = mysql_num_rows($rsOld);

$oldMls = array($row_rsOld['data']);

mysql_select_db($database_name, $queryTwo);
$query_rsNew = "SELECT data FROM tableTwo";
$rsNew = mysql_query($query_rsNew, $queryTwo) or die(mysql_error());
$row_rsNew = mysql_fetch_assoc($rsNew);
$totalRows_rsNew = mysql_num_rows($rsNew);

$newMls = array($row_rsNew['data']);

$difference = array_diff($newData, $oldData);

 foreach ($difference as $key => $value) {
        echo "<br />Key: $key; Value: $value\n"; }

When I execute the script above I get the following output:

Key: 0; Value: 107034

It is only pulling one record when there are at least 15 unique columns that need to be pulled. Any ideas on how to achieve this better or fix this script??

    I'd think you could do this with pure SQL:

    (SELECT t1.data FROM tableOne t WHERE t1.data NOT IN (SELECT t2.data FROM tableTwo t2))
UNION
(SELECT t3.data FROM tableTwo t3 WHERE t3.data NOT IN (SELECT t4.data FROM tableOne t4))

    May not be the most elegant SQL and others here may be able to optimize it, but I believe that's what you're going for. The first part gets all the entries in tableOne that aren't in tableTwo and the second gets all the entries in tableTwo that aren't in tableOne.

      Thank you for your input. I got stuck working with my sql query so I went to the php to find the solution. You are right though, the best way to achieve this is directly in the sql so I can use the data better. Here is the solution that I found to work for me.

      SELECT data
      FROM tableOne LEFT JOIN tableTwo ON tableOne.data = tableTwo.data
      WHERE tableOne.data IS NULL

      Thank you very much for your time and input

        Glad you got it. Just one question as the answer doesn't stand out to me from your query. You have where tableOne.data IS NULL, so that suggests that you're finding the entries in tableTwo that aren't in tableOne, but what about the converse?

        I could be missing something, though, as SQL isn't my best area.

          I only need to check one table against the other because one table is a temporary table. Basically, I have one table the I work with and I use the second to check against it. The information is provided to me by a service provider which I download and use for various purposes. What I needed to achieve from this query was to find the new rows in the datafeed before I update the main database. The datafeed has no way to identify the new records other than running a comparison on the new feed compared to the old. So once I find the new records I can notify users of the new information and what it is. This is a daily task which I do alot of manipulation to the data to show it in various ways. I found this to be the best way to handle the task even though it will increase the script length quite a bit to do.

          Hope this helps explain my purpose

            Write a Reply...