If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > PHP > Connect to two mdb access 97 to merge two rows and to find duplicates

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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";
?>
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,081
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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";
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,081
Quote:
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Tags
access, sql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On