transfer unique records from one mysql table to another

[roshanjameer]

hi,

i have a table.in that there are nearly 12000 email ids.in that may be 1000 email ids duplicates(repeated twice).i want to transfer the unique email ids from old table to new table.i tried the code but it is transfering the all ids but not unique.how to solve this.any of your help will be appreciated.here is my code
<?php
$conn4=mysql_connect("localhost","","");
mysql_select_db("mrj",$conn4);
$sql="INSERT INTO cc (email) SELECT DISTINCT email FROM bb";
$result=mysql_query($sql,$conn4) or die(mysql_error());
if($result)
{
echo "transfered";
}
else
{
echo "not";
}
?>

thanks
mrjameer

[NightShift58]

Often, what is "unique" to you is not the same as what is "unique" for the database...

So we have to define "unique" for you and see if we can get the database to do it that way.

Also, in you INSERT/SELECT statement, you are only using 1 field ("email"). Is that right? And, is that what you want as unique in the new table?

[roshanjameer]

hi nightshift58,

the old table has 2 fields.
1.id int primarykey autoincrement
2email text

values
1--abc@yahoo.com
2--bbb@hotmail.com
3--abc@yahoo.com

the new table has same 2 fields id,email with same structure.i want to transfer only unique(distinct) records(abc@yahoo.com,bbb@hotmail.com) into new table.i want to avoid duplicate records


thanks
mrjameer

[NightShift58]

Your INSERT...SELECT is not the problem. It should do exactly what you described.

In the future, however, if you want "email to be unique, you should define it as a unique key in the table. That will prevent duplicates from being inserted in the future.

Back to the problem...

Did you remember to DELETE/TRUNCATE the table "cc" before doing the INSERT?

Do you haves spaces in some of the fields?
Are all "email" in the same case, i.e. lower case?

Just to be sure, you could try:
INSERT INTO cc (email) SELECT DISTINCT lower(trim(email)) FROM bbYour script could look like this:<?php

$conn4 = mysql_connect("localhost","","");
mysql_select_db("mrj",$conn4);

$sql1 = "TRUNCATE TABLE cc";
$result1 = mysql_query($sql1,$conn4) or die(mysql_error());

$sql2 = "INSERT INTO cc (email) SELECT DISTINCT lower(trim(email)) FROM bb";
$result2 = mysql_query($sql2,$conn4) or die(mysql_error());

if($result2) {
echo "transfered";
$sql3 = "SELECT email FROM cc ORDER BY email";
$result3 = mysql_query($sql3,$conn4) or die(mysql_error());
if ($result3) {
while ($row = mysql_fetch_assoc($result3)) {
print $row['email'] . "<br>";
}
}
} else {
echo "not";
}
?>

[roshanjameer]

hi,

if i insert the records manually in to a table and transfer those records to new table the distinct keyword is working.but if i transfer the already existing records of a table into new table the distinct keyword is not working


thanks
mrjameer

[NightShift58]

The distinct keyword IS working. If it didn't, there would be a worldwide Geek revolution...

The "distinct" only affects what you're SELECTING but NOT what you are INSERTING. Big difference... Have your tried the script I posted?

It works here...

[roshanjameer]

hi nightshift58,

the email-ids in table are as follows

JddCff@hotmail.com
Jedctoo@hotmail.com
JAMD602@hotmail.com
JamDaLfgh@hotmail.com

i tried your code.it is transfering the all email-ids into lowercase and inserting in to new table with duplicates

thanks
mrjameer

[NightShift58]

Here's a new version of the code to test:<?php

//$conn4 = mysql_connect("localhost","","");
//mysql_select_db("mrj",$conn4);
include "DBconnect.inc.php";

$sql = "SELECT email FROM bb ORDER BY email";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
print "<hr>Table bb";
while ($row = mysql_fetch_assoc($result)) {
print $row['email'] . "<br>";
}
}

$sql = "SELECT email FROM cc ORDER BY email";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
print "<hr>Table cc BEFORE delete<br>";
while ($row = mysql_fetch_assoc($result)) {
print $row['email'] . "<br>";
}
}

$sql1 = "TRUNCATE TABLE cc";
$result1 = mysql_query($sql1) or die(mysql_error());

$sql = "SELECT email FROM cc ORDER BY email";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
print "<hr>Table cc AFTER delete<br>";
while ($row = mysql_fetch_assoc($result)) {
print $row['email'] . "<br>";
}
}

$sql2 = "INSERT INTO cc (email) SELECT DISTINCT lower(trim(email)) FROM bb";
$result2 = mysql_query($sql2) or die(mysql_error());

if($result2) {
echo "<hr>Table cc AFTER insert<br>";
$sql3 = "SELECT email FROM cc ORDER BY email";
$result3 = mysql_query($sql3) or die(mysql_error());
if ($result3) {
while ($row = mysql_fetch_assoc($result3)) {
print $row['email'] . "<br>";
}
}
} else {
echo "not";
}
?>
See it live: http://www.nightshift58.com/webdev/test_unique_insert_select.php

[bokeh]

i tried your code.it is transfering the all email-ids into lowercase and inserting in to new table with duplicatesHere's a new version of the code to test:$sql2 = "INSERT INTO cc (email) SELECT DISTINCT lower(trim(email)) FROM bb";Why?

If you are getting duplicates it is because that column of the table should be UNIQUE but is not.

[roshanjameer]

hi nightshift58,

i tried your new code still it is inserting the duplicate records.live example is working good.both tables 'bb' and 'cc' have the email field as unique.the recods in to bb table has been inserted from a text file.

textfile--bb table -- cc table

thanks
mrjameer

[NightShift58]

I don't know why that would happen.

In my code, I delete whatever is in table "cc" and then I insert unique emails.

The - predictable - result is that no duplicates are inserted into the table "cc".

I changed my test data here. I've added spaces before and after the emails.

Still, as you can see under http://www.nightshift58.com/webdev/test_unique_insert_select.php, it works here...

So either I don't understand what you're telling us or somewhere, something is very wrong...

[NightShift58]

Why?
I know I only need the trim for the left trim.

I lowered everything for his visual.

I don't understand the issue with his duplicates, I think... Something's odd here...

[NightShift58]

the recods in to bb table has been inserted from a text file.
textfile--bb table -- cc tableIt doesn't really matter where the records came from, once they are in table "bb", they have to follow the rules of MySQL...

Maybe you can post a dump of the table...

[roshanjameer]

hi,

i have created the new tables and tested now it is working.thanks a lot.


thanks
mrjameer

[bokeh]

I don't understand this either:Table bb ALL records
[ aBc@yahoo.com]
[ AbC@yahoo.com]
[BBb@hotmail.com]
--------------------------
Table bb UNIQUE records
[aBc@yahoo.com]
[BBb@hotmail.com]
aBc and AbC are two unique email addresses but UNIQUE see them as the same. Is this normal MySQL behaviour?

[NightShift58]

aBc and AbC are two unique email addresses but UNIQUE see them as the same. Is this normal MySQL behaviour?Yes, it is.On character type columns, sorting — like all other comparison operations — is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.In this case, it's actually okay. The values may be slightly different, but as far as email addresses, they are indeed "identical" in the functional sense - i.e. mails would land in the same mailbox - which is probably why the OP wants to remove duplicate, or, better said, only export uniques.

[bokeh]

they are indeed "identical" in the functional sense - i.e. mails would land in the same mailboxAccording to RFC2821 (http://www.faqs.org/rfcs/rfc2821.html): "The local-part of a mailbox MUST BE treated as case sensitive. Therefore, SMTP implementations MUST take care to preserve the case of mailbox local-parts. [...] In particular, for some hosts the user "smith" is different from the user "Smith". "

[NightShift58]

Could be... but according to my mailbox(es) it ain't ne'er been thataway. :)

[bokeh]

Could be... but according to my mailbox(es) it ain't ne'er been thataway. :)My mail exchanger is case insensitive too (but 2 cases isn't exactly a big cross section). What surprised me though is UNIQUE is case insensitive.

[NightShift58]

Starting as an internet provider in Germany in 1997 until today, retired in "2 places at the same time", I've yet to run into a mail system that was case sensitive. It doesn't mean that there aren't, yet it would surprise me.

Quoting from the same RFC (first time I read it): While the above definition for Local-part is relatively permissive, for maximum interoperability, a host that expects to receive mail SHOULD avoid defining mailboxes where the Local-part requires (or uses) the Quoted-string form or where the Local-part is case-sensitive. For any purposes that require generating or comparing Local-parts (e.g., to specific mailbox names), all quoted forms MUST be treated as equivalent and the sending system SHOULD transmit the form that uses the minimum quoting possible.Maybe that explains the "insensitivity" on the part of most systems, if not all...

As far as UNIQUE is concerned, what I had quoted came from MySQL - so that possibly limits the scope of that. And the quote refers to ORDER BY, but it does the same to DISTINCT. I really don't know how it SHOULD be, just how it is. And it' been that way since dBaseII only ran on Apple][.

Don't hit me too hard on the theoretical stuff - I usually don't have much of a clue. I can usually make it work, though.

How's the weather around your parts? Going that way on Friday. Do I need arctic winter clothes?

[bokeh]

Quoting from the same RFCThe document almost contradicts itself. Fist it says: "The local-part of a mailbox MUST BE treated as case sensitive.", then it says "exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged.". I guess its one of those thing like single quoted attributes in HTML mark-up, valid but non standard and rarely seen (I've noticed NogDog uses them).

As far as UNIQUE is concernedEven more strange :I've found UNIQUE is case sensitive on my *NIX platform but not under Windows XP. (Maybe it's just a configuration issue).

How's the weather around your parts? Going that way on Friday. Do I need arctic winter clothes?Not too bad considering it's the middle of winter but it feels freezing to me. 22 degrees at the minute (72 Fahrenheit), RH 32%, completely clear sky. Sunrise 08:19 sunset 18:01. Getting down to 8 degrees at night.

[NightShift58]

Even more strange :I've found UNIQUE is case sensitive on my *NIX platform but not under Windows XP. (Maybe it's just a configuration issue).It must be because mine is as well - Bravenet - and you saw what it delivers...