Counting occurences in MySql

[countrydj]

I am writing a simple script to collect email addresses.
I want to collect ALL email addresses, even if they are duplicated.
However, I don't want to add the emaill address each time. I want to identify that the email address is already registered and then have another field [number] that will give the total number of times that the email address has been registered.
e.g The first time an email address is registered, it will put 1 into [number].
The second time the email address is registered it will put 2 into [number]
The third time the email address is registered it will put 3 into [number]
etc...

Up to now I have got:

PHP Code:

if($email != '') {
        $qry = "SELECT * FROM bf_users WHERE email='$email'";
        $result = mysql_query($qry);
        if($result) {
            if(mysql_num_rows($result) > 0) { 


This will check if the email address already exists in the database, but I am now stuck.
Any help and advise will be very welcome.

Thanks,

[Fou-Lu]

This isn't really a PHP issue.
The first approach you can do is to not flag the address as unique and allow multiple inserts. This only works if the logic of the design allows a many to many relationship. This can then accept a count.
Another approach would be to simply use an INSERT ON DUPLICATE KEY UPDATE syntax to issue an update when it finds a duplicate. The documentation for that is here: http://dev.mysql.com/doc/refman/5.0/...duplicate.html

[countrydj]

Many thanks Fou-Lu..

This is probably why I couldn't work out how to do it.
I will have a look at your suggestion and see what I can make of it.
Failing that I will have to figure out another way of dealing with duplicates, or even accept duplicates.
My reason for not wanting to accept duplicates is so that if I send out a mail-shot, I don't want to send it to the same email address a number of times.
At the same time, I don't want to restrict the number of times that a user can submit their email address in order to get what is on offer.

Thank you,

Regards

[countrydj]

Hi Fou-Lu..

I have had a look at the link you gave me for DUPLICATE KEY, but, me being 'thick' I can't understand it.
I can see now that it is a mysql issue and not a php issue.
I am going to repost my problem in the MySql section of this forum.
Please don't think that I don't appreciate you help. I do !!!

Thank you...