transfer unique records from one mysql table to another

[mrjameer]

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

[HuggieBear]

The code looks fine to me.  What happens if you execute it in phpMyAdmin?

Regards
Huggie

[mrjameer]

hi,

i executed it in phpmyadmin but it is showing all the rows.i have created a table with only id,email fields.inserted 5 records with some duplicates and executed to display the distinct records.it is working but when i transfer the this data in to some other it is not working.


thanks
mrjameer

[HuggieBear]

Can you produce a data dump of the new table you've created in phpMyAdmin and then post it here.

Regards
Huggie

[mrjameer]

the records for this bb table has been dumped from text file.the code for moving data from text file to table is
<?php
$conn4=mysql_connect("localhost","","");
mysql_select_db("mrj",$conn4);

$fp = fopen("file.txt", "r");

if($fp)
{
  while( !feof($fp) )
  {
      $line = fgets($fp, 1024);
      list($email) = explode(',', $line);

      // do inserts here, i.e.
      $query = "INSERT INTO bb (email) VALUES
                ('$email')";
      $result=mysql_query($query,$conn4);

  }
    echo "ok";
}
else
{
  echo 'unable to open file for read';
}
?>


CREATE TABLE `bb` (
  `email` text NOT NULL
) TYPE=MyISAM;

#
# Dumping data for table `bb`
#

INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');
INSERT INTO `bb` VALUES ('[email protected]\r\n');


CREATE TABLE `cc` (
  `email` text NOT NULL
) TYPE=MyISAM;

[HuggieBear]

OK, I used the file you provided with the following SQL...

[color=blue]INSERT INTO cc (email) SELECT DISTINCT email FROM bb[/color]

...and it worked fine.  There's nothing wrong with that code.

Regards
Huggie