Re: Add New Records Only!

"Satyam" <Satyam@xxxxxxxxxxxxx> · Fri, 23 Mar 2007 17:51:14 +0100

Delete from table where id = (select min(id) from table group by field1, 
field2 ,...  having count(id) > 1)

Id is the unique primary key, fieldn are the fields that can be duplicated. 
Each time this runs it will remove one occurence of the all duplicated 
records.  You'd have to run it several times until it deletes no more 
records.   In MySql, you cannot have the same table in the 'delete' and in 
the subquery, thus, you will have to first insert the id's in an auxiliary 
table and then delete the records.

Satyam

----- Original Message ----- 
From: "Rahul Sitaram Johari" <sleepwalker@xxxxxxxxxxxxxxxx>
To: "Mark" <markw@xxxxxxxxxxxxxx>; "PHP" <php-general@xxxxxxxxxxxxx>
Sent: Friday, March 23, 2007 5:24 PM
Subject: Re:  Add New Records Only!

Ave,

"Three: Insert everything and remove duplicates later."

Out of the suggested options, this option is sounding the most sane
attainable on my end. I don't have a complete grip on how to accomplish
this, but certainly sounds feasible. Let me look at ways to achieve this.

Thanks!

On 3/23/07 11:36 AM, "Mark" <markw@xxxxxxxxxxxxxx> wrote:

Rahul Sitaram Johari wrote:

As far as I can see, there is probably only three ways to do this:

One: Make sure your dbase system contains unique primary key capability, 
and
use it to avoid duplicates.
Two: query for the row, if it isn't there insert it. (You'll have to deal
with concurrency with locking or something)
Three: Insert everything and remove duplicates later.

Ave,

Let me explain what I¹m trying to do. Unfortunately it¹s not a very 
simple
Add/Update transaction.
Basically I gather records from multiple mySQL tables and add them to a
DBF
(dbase) database.  I wrote a code which was able to accomplish this
without any problems and the add_records goes very smooth.

However, I have to modify this program now so that only ³New² records,
i.e., records that have not yet been transferred, are added into the 
DBF.
In other words, any records that are being added, which already exist in
the DBF, should not be added.

If it were just adding records from one mySQL to another mySQL, I could
easily use INSERT IGNORE or the Unique Key fundamental  however, since
I¹m adding from multiple mySQL tables into DBF, I¹m not sure how to go
about doing this.

Here¹s my code that takes records from multiple mySQL tables and adds 
them
to a DBF.

// define the array with mySQL Table Names & Identifier
    $tChoice = array(
       "lodispo" => "VB",
       "lodispo_osma" => "ATL",
       "lodispo_osmh" => "HOU",
       "lodispo_osmn" => "NSV",
       "lodispo_osmp" => "PAA",
       "lodispo_osmj" => "JAX",
       "lodispo_osmt" => "TPA",
       "lodispo_dmam" => "MET",
       "lodispo_osmf" => "FTM",
       "lodispo_hfglend" => "GLEND",
          "lodispo_hfmesa" => "MESA",
          "lodispo_hfphenx" => "PHENX",
    );

    // open DBF in read-write mode
    $db2 = dbase_open($theDBFfile, 2);
    if (!$db2) {
      echo "<BR><BR><STRONG>Fatal Error:</STRONG> Unable to open
database<BR><BR>";
      exit;
    }

    else {

            // Let's Run Array Loops!
            foreach ($tChoice as $tblQ => $bxQ) {

            // connect to mySQL tables one by one
            mysql_select_db($database_imslead_transfer,
            $imslead_transfer); $query_loDispo = "SELECT * FROM $tblQ";
            $loDispo = mysql_query($query_loDispo, $imslead_transfer) or
die(mysql_error());
            $row_loDispo = mysql_fetch_assoc($loDispo);
            $totalRows_loDispo = mysql_num_rows($loDispo);

            // write mySql data to Dbf
            do {
                 dbase_add_record($db2, array(
                     $row_loDispo['phone'],
                     $row_loDispo['comments'],
                     $row_loDispo['starttime'],
                     $row_loDispo['endtime'],
                     $row_loDispo['dispo'],
                     $row_loDispo['loanofficer'],
                     $row_loDispo['verifier'],
                     $bxQ));
                 } while ($row_loDispo = mysql_fetch_assoc($loDispo));
            }

    dbase_close($db2);
    }

Any suggestions would be highly appreciated.

Thanks,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Rahul Sitaram Johari
CEO, Twenty Four Seventy Nine Inc.

W: http://www.rahulsjohari.com
E: sleepwalker@xxxxxxxxxxxxxxxx

³I morti non sono piu soli ... The dead are no longer lonely²

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 
22/03/2007 7:44

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php