Hey everyone,

I have been on the road for the last 18 days and I am not scheduled to be home for another 10 days. I dont think my brain is working today. I use mysql everyday, but for some reason I am having problems finding new records in my database. Here is what is happening. I download data everyday from a data source and I need to find new records that are entered into this data source. To do this I created two tables with the same feilds in both. When I download the source data I put it in one table called "new" - I then want to compare the two databases and find all the new records so that I can email the new ones to selected people. Then once that is complete I move the data located in "new" to the main table and delete all the data located in "new". I have tried doing a loop with php that looks for new records by trying:

<?php   if ($oldMls <> $newMls) { do {?>
  <?php echo $row_rsNew['mls_num']; ?><br />
  <?php } while ($row_rsNew = mysql_fetch_assoc($rsNew)); } ?>>

But this displays all records not the new ones (something tells me that the if statement should be located in the loop instead of starting the loop?)

I have also tried doing this task in the mysql query by trying:

SELECT tableNew.data, tableMain.data
FROM tableNew, tableMain
WHERE tableMain.data <> tableNew.data
GROUP BY tableNew.data

This is not working right either.

I dont think I am heading in the right direction with this. Can anybody put me back on the right track with the best way to achieve this?

Thanks in advance!

    Why not record the day and just select those records with the given day?

      Because that would of made it to easy:}

      That was my first thought as well, but the source provider does not give that data in a good way. There is an option that I can use that is basically a number of days in the system, but the dates vary all the time. Once piece of data will say 1 day and another will say 3 days some 5 days even when they are new to the databse. Its not a very accurate system. The date they provide is based on when a contract was signed, not when it was entered into the system. Thats why the dates are so off and not exact enough to use for this type of record location. It would be great if they fixed this as it would make it alot easier on me!

      Thanks for your reply.

        Ah, I actually suggested the date thing because I misunderstood your problem, but now I see that it can be used after all 😉

        1. Determine what columns are to be used to identify a record. In other words, if I provided you with two records that have the exact same data in these columns, you would tell me that one is a duplicate.
        2. Create a combined unique constraint on these columns.
        3. Add a column for the day when you first collected the data from the source.

        Now, whenever you collect data from the source, just attempt to insert, recording the current day, and ignore any unique constraint violations. To find the new records, just select those that were inserted on the most recent data collection day.

          Thank you for your input. I looked into going in that direction and a the proper sql query come to me. 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

            Good to see that you have found a solution, but I note that my suggestion dispenses with the extra table. Sure, my solution requires data to be declared unique, but in your current solution data would need to be indexed anyway so that the join will be efficient.

              Write a Reply...