Page 1 of 1
Help finding duplicates in 2 databases
Posted: Sun Oct 02, 2005 6:48 pm
by Extremest
I have 2 db's with the same tables. Using php how can I grab entries from a certain field on one db table and check for it to be in the other db table. If it is then skip it if not then keep it. I can setup the write to file just need to know how to do the other part of this.
Posted: Tue Oct 04, 2005 5:36 pm
by Extremest
Jcart | Please use Code: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
ok this is the code I have come up with, but it don't quite seem to work and it is a cpu hog with mysql also for some reason and very very slow. Can someone please tell me what I am doing wrong.
Code: Select all
$database = "md5";
$dbmaster = "md5new";
$mysql = mysql_connect("localhost", "root", "password");
$mysqle = mysql_connect("localhost", "root", "password");
$filename = 'c:/temp/temp/list.txt';
if (!$handle = fopen($filename, 'a')) {
echo "Cannot open file ($filename)";
exit;
}
for($i=0; $i<256; $i++) {
$table = str_pad(dechex("$i"),2,"0",STR_PAD_LEFT);
$q = "select text from `$table`";
$result = mysql_db_query($database,$q,$mysql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$test = $row["text"];
$m = "SELECT text FROM `$dbmaster`.`$table` where text=\"$test\" limit 1";
echo $m;
$result_master = mysql_db_query($dbmaster,$m,$mysqle) or die(mysql_error());
$rows = mysql_fetch_array($result);
$_result = $rows["text"];
if ($rows["text"] != $row["text"]){
fwrite($handle, $row["text"]);
fwrite($handle, "\n");
}
}
}
Jcart | Please use Code: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Tue Oct 04, 2005 6:58 pm
by Extremest
ok couple screw ups in the first one. I changed so that it actually does the right result set for the check and also removed the second connect so that it uses one connect for both. It does work yet it is very slow and mysql kills on the cpu at 100%. Is there any optimization that I can get out of this.
Posted: Tue Oct 04, 2005 7:33 pm
by feyd
Posted: Tue Oct 04, 2005 8:29 pm
by Extremest
I checked that out but can't figure out how to use it with 2 seperate db's.
Posted: Tue Oct 04, 2005 8:36 pm
by feyd
the table reference can include a database reference as well. Pure example:
Code: Select all
SELECT * FROM `database`.`table` INNER JOIN `database2`.`table` USING( `somefield` )
Posted: Tue Oct 04, 2005 8:37 pm
by Extremest
This query will give me the duplicates yet also gives every field in both db for that row. How can I get it to just output the text column only. If I try to replace * with text it comes up saying that column text in field list is ambiguous.
Code: Select all
SELECT * FROM `md5`.`00`, `md5new`.`00` where `md5`.`00`.`text` = `md5new`.`00`.`text`
Posted: Tue Oct 04, 2005 8:41 pm
by feyd
when dealing with sources that contain the same column names, you have to specify exactly which source field you want.. for instance `md5`.`00`.`text`
Posted: Tue Oct 04, 2005 8:45 pm
by Extremest
ok that will show me the ones that are dupes if I change that to != in the where clause will I get the ones that are unique then?
Posted: Tue Oct 04, 2005 8:46 pm
by Extremest
ok that don't work. I will have to figure out a way to have php take my dump of all the text fields with the ones that match and have them removed from it. Maybe loaded as an array or something.