Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,225
    Thanks
    23
    Thanked 606 Times in 605 Posts
    The way I understand it you want to increase the emailcount column if it exists and if not add a new person. Thry this:
    Code:
    <?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!"

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You can use the INSERT ON DUPLICATE KEY syntax.
    Code:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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,
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •