Retrieve Last Duplicate Record

[refiking]

I have a db field name "id" that is autoincrement and I want to run the query to get the most recent record that matches the query.  What do I need to do to accomplish this?

 

$sql = mysql_query("SELECT * FROM `phplist_user_user` WHERE `email` = '$email'")or die(mysql_error());

[Mchl]

'most recent' implies using some kind of timestamp. You cannot rely on auto_increment to indicate the order in which record were inserted.

 

If you want to take a risk though:

SELECT * FROM `phplist_user_user` WHERE `email` = '$email' ORDER BY id DESC LIMIT 1

 

[refiking]

When I ran that, I got this error:

 

Duplicate entry 'my@email.com' for key 2

[Mchl]

Hardly possible. It's not this query that generated this error.

[refiking]

You're right.  It's this query:

mysql_query("INSERT INTO  `phplist_user_user` (`email`,`confirmed`,`htmlemail`,`paystatus`) VALUES ('$email', '1', '1','$id')")or die(mysql_error());

[Mchl]

And it said you tried to insert 'my@email.com' into a column on which a UNIQUE index is created, where such entry already exists.

[refiking]

ahhh ok.  So, should I change it from unique to something else?  Or should it be deleted altogether?

[Mchl]

I don't know. Do you want to allow non-unique e-mails in this column?

[refiking]

yes

[Mchl]

So you shouldn't have UNIQUE index on this column.