If duplicate exists, I want to read all entries for that ID

[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 email address each time. I can identify that the email address is already registered and has an emailcount of 1 (this is in a separate field. The 1 is registered during the first registration)

My fields are:
|id |fname|surname|email |emailcount|
|23|John |Doe |jd@domain.com|1 |

When jd@domain.com is registered a second time, I want to read the whole line and pick out 'emailcount'.
I can then add 1 to the number and use UPDATE to modify it to the database.

This is the code that I have got up to now:

PHP Code:

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

                $errflag = true;
            }
            @mysql_free_result($result);
        }
        else {
            die("Query failed");
        }
    }
    
    if($errflag) {


//#########################################
print "Duplicate email: "$email; 

//######################################### 


I cannot work out the next php code to achieve this.
I would appreciate any help and advice.

Thanks

[sunfighter]

The way I understand it you want to increase the emailcount column if it exists and if not add a new person. Thry this:

<?php
$query = "select emailcount from bf_users where email = '$email'";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
if($row)
{
	$emailcount = $row[0];
	$emailcount++;  // ADD ONE TO THE $emailcount
	$query = "update bf_users set emailcount = $emailcount where email = '$email'";  // PUT NEW $emailcount INTO THE emailcount
	$result = mysql_query($query);  
}else{
	INSERT INTO bf_users ........;  // IF A NEW PERSON PUT HIM INTO DATABASE
}
?>

A fan of Johnny Cash ever since "Cry! Cry! Cry!"

[Fou-Lu]

You can use the INSERT ON DUPLICATE KEY syntax.

INSERT INTO table (email) VALUES ('an@email.c') ON DUPLICATE KEY UPDATE acountercolumn = acountercolumn + 1

Then you can simply SELECT acountercolumn FROM table WHERE email='an@email.c'. That assumes that the email column would have a unique or primary key constraint.

[Old Pedant]

I don't think you need the separate query to get the counter column *unless* the count is more than 1.

When you do the insert and do *NOT* get the duplicate key (meaning the counter will be 1), MySQL reports the number of rows affected as just 1. Whereas, when you do the insert and there is a duplicate, MySQL reports that 2 rows are affected. So you would only need to go inquire about the new count when the rows affected value is not 1.

It's a minor improvement, and if most of your inserts turn out to really *be* updates, then it's not worth the coding. But if, say, 75% of your inserts are new email addresses then it might be worth it.

[countrydj]

Hi Guys...
Thank you very much for your suggestions.
I actually used 'sunfighter' suggestion. I understood it better.

However, I had a lot of work to do (for me) to get the script working how I wanted it to work.

The script is a 'double opt-in' script (I don't want false email addresses).
The original script had a $action=subscribe field sent from the form.
It then sent out an email to the given email address, with the required info to click on and send the info back to the script.
In order to use UPDATE feature, I had to include an $action=change in the validation email that was sent out to be clicked on.
I also used md5("$secret_code $formatted_email") as an extra validation.

Because I am a novice, it took me quite a while to get it right, but it now works.

Thank you very much.

Kind regards,