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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Counting occurences in MySql

    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,
    Last edited by countrydj; 01-19-2013 at 04:43 PM.
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  • #2
    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
    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
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    countrydj (01-19-2013)

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

  • #4
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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...
    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
    •