Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Connect to two mdb access 97 to merge two rows and to find duplicates

    Hello!

    I have two access 97 mdb, and thanks to ODBC I'm able to "access" with php (I've installed XAMPP).

    The problem is that I have

    MDB1.TABLE1 and MDB2.Table2
    rFactura rFactura
    00001 00003
    00002 00006
    00004
    00005
    00007

    That shoul be normal, but some times I could have this

    MDB1.TABLE1 and MDB2.Table2
    rFactura rFactura
    00001 00003
    00002 00006
    00004
    00005
    00006
    00007

    00006 is "duplicate" so How Can I find out this?


    So far I Have 1 mdb, but how to connect both, merge mdb1.rfactura and mdb2.rfactura find duplicate, I dont know....


    Solution so far,



    <?php
    $conn=odbc_connect('MDB1','','');
    if (!$conn)
    {exit("Connection Failed: " . $conn);}

    $sql = "SELECT rfactura FROM MDB1 group by rfactura having count(*) >= 2";
    $rs=odbc_exec($conn,$sql);
    if (!$rs)
    {exit("Error in SQL");}

    while (odbc_fetch_row($rs))
    {
    $factura=odbc_result($rs,"rfactura");

    echo "$factura<br>";
    }

    odbc_close($conn);
    echo "Done";
    ?>

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    im confused
    you have two Access db's, you are using PHP
    yet you are posing the question in the MySQL forum?

    what defines a row as a duplicate?
    I can't see the logic of how rows appear in one or the other db.

    if your problem is that the same rfactura value appears in both DB's then you can identify the duplicates by using a JOIN and decide what to do to elimiate duplicates

    ege
    SELECT t1.rfactura from MDB1
    join MDB2 as t2 on t2.rfactura = t1.rfactura
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Thanks in advance healdem!
    I'm a little concern about this and ashamed because is all new to me

    Quote Originally Posted by healdem View Post
    im confused
    you have two Access db's, you are using PHP
    yet you are posing the question in the MySQL forum?
    I thought mysql people was more used to do this things using ODBC and php

    Quote Originally Posted by healdem View Post
    what defines a row as a duplicate?
    I can't see the logic of how rows appear in one or the other db.
    When some row in mdb1 is checked, it's moved to mdb2, and some times an old system could add a duplicated. But I don't have the source.
    All records should be, combinated, sequential, no duplicates, they are in total about 400000 records (both mdb1+mdb2).

    Quote Originally Posted by healdem View Post
    if your problem is that the same rfactura value appears in both DB's then you can identify the duplicates by using a JOIN and decide what to do to elimiate duplicates

    ege
    SELECT t1.rfactura from MDB1
    join MDB2 as t2 on t2.rfactura = t1.rfactura
    I have to identify the duplicates only, and I don't work often with this :/

    $conn=odbc_connect('mdb1','','');
    if (!$conn)
    {exit("Connection Failed: " . $conn);}
    $conn1=odbc_connect('mdb2','','');
    if (!$conn1)
    {exit("Connection Failed: " . $conn1);}

    $sql = "SELECT t1.rfactura from mdb1 join mdb2 as t2 on t2.rfactura = t1.rfactura";

    $rs=odbc_exec($conn,$sql);
    if (!$rs)
    {exit("Error in SQL");}


    while (odbc_fetch_row($rs))
    {
    $guia=odbc_result($rs,"rfactura");

    echo "$guia<br>";
    }

    odbc_close($conn);
    odbc_close($conn1);
    echo "done";

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    I thought mysql people was more used to do this things using ODBC and php
    until you have your PHP script debugged and working (and Im guessing you haven't, then I'd class this as a PHP question.
    but whatever else it is it isn't an MySQL quetion

    oops
    forgot to add the table alias above
    either
    Code:
    SELECT t1.rfactura from mdb1 as t2 join mdb2 as t2 on t2.rfactura = t1.rfactura
    OR
    Code:
    SELECT mdb1.rfactura from mdb1 join mdb2 on mdb1.rfactura = mdb2.rfactura
    if the process is rows are moved from mdb1 to mdb2, anbd that soemtimes erroneous rows appear in mdb1
    then you could delete the duplicates in mdb1, by identifying the rows with duoplicates

    eg
    Code:
    delete * from mdb1 join mdb2 on mdb1.rfactura = mdb2.rfactura
    you can helkp yourselfd in your php script by using the 'or die' construct
    PHP Code:
    $sql "SELECT t1.rfactura from mdb1 join mdb2 as t2 on t2.rfactura = t1.rfactura" or die (echo 'SQL Error! MySQL Error:'.mysql_errno.';'.mysql_error
    however bear in mind that if you are using PHP 5.5.0 or above you shoudl be using the MySQLi or PDO_MySQL libraries
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •