Re: Add New Records Only!

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@rahulsjohari.com> To: "Mark" <markw@mohawksoft.com>; "PHP" <php-general@lists.php.net> Sent: Friday, March 23, 2007 5:24 PM Subject: Re: [PHP] 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@mohawksoft.com> 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@rahulsjohari.com ³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