Mysql Distinct not working?

[tiburblium]

I must be doing something wrong because when I run this query:

Select distinct address from mailout

It doesn't eliminate duplicate values. from field address from table mailout...All i want to do is elminate duplicate rows from a single field in a single table. I can't seem to do it with the built in mysql function but that doesnt make sense to me. Anyway until i can get that figured out im trying to get it to work with php:
by reading the rows from one table and deleting the duplicates with php, a very sloppy approach but best i could do, sorry. Anyway i think im close but still not working:

<?php

mysql_connect("localhost", "root", "");
mysql_select_db("mailout");
$query = "SELECT * FROM mailout";
$result = mysql_query($query);

while ($row = mysql_fetch_array ($result))
{

$query3 = "DELETE * from mailout WHERE id = '$row[id]'";
$result3 = mysql_query($query3) or die(mysql_error());

$query2 = "SELECT * FROM mailout";
$result2 = mysql_query($query2);
while ($row2 = mysql_fetch_array ($result2))
{
	if($row[address] == $row2[address])
	{
		$switch = "no";
	}
}


if($switch == "no")
{
	print "<table border=1><td>Skipping ($row[address])</td></table>";
}

else
{

$query2 = "INSERT INTO mailout2 (address, city, state, zip, contactname, name, serialnumber, model) VALUES ('$row[address]', '$row[city]', '$row[state]', '$row[zip]', '$row[contactname]', '$row[name]', '$row[serialnumber]', '$row[model]')";
$result2 = mysql_query($query2) or die(mysql_error());



print "<table border=1><td>$row[address]</td><td>$row[city]</td><td>$row[state]</td><td>$row[zip]</td><td>$row[contactname]</td><td>$row[name]</td><td>$row[serialnumber]</td><td>$row[model]</td></table>";

}

}

?>

any ideas on either approach would be appriacted thanks, alll im trying to do is eliminate duplicate values in rows

[Trs2988]

I'm a little confused about what you're doing, but I'll try to offer some help.

First of all, you're trying to delete rows incorrectly. The correct syntax is "delete from tablename" not "delete * from tablename".

Next, if I understand correctly, you're trying to go through a table and delete all rows that have duplicate addresses, so that you only have one row per address. Pretty simple, if that's what you're going for.

Also, for the sake of this example, I'm going to assume that id is the primary key in your table.

$result = mysql_query("select * from mailout");
while($row = mysql_fetch_assoc($result)) {
extract($row);
mysql_query("delete from mailout where address='$address' AND id<>'$id'") or die (mysql_error());
}

Here's what it does, if you don't understand. First, it goes through each row. Then, it deletes all rows with the same address that DOESN'T have that same id. Therefore, you're left with a single row per address.

Hope that helps.

[tiburblium]

Thanks for the help, got it to work now!!!