Help finding duplicates in 2 databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Help finding duplicates in 2 databases

Post 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.
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post by Extremest »

Jcart | Please use

Code: Select all

and

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

and

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]
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Useful Posts wrote:Find all records that aren't matching: SQL SELECT JOIN HELP
may be of interest.
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post by Extremest »

I checked that out but can't figure out how to use it with 2 seperate db's.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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` )
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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`
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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?
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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.
Post Reply