How to insert duplicate value depending on what value was entered using mysql and php

Question

I tried to summarize it on my title but let me explain you what I am trying accomplish in more details below.

Currently I pass an array to my query and use a foreach to insert each array in to my db as shown below.

if(is_array($myArr)){
 foreach($myArr as $userID=> $email){
  $sql = "INSERT INTO userdata (userID, email) values ('$userID', '$email')";
  mysql_query($sql) or exit(mysql_error());
 }
}

Currently my array looks the following

Array
(
[39] => [email protected]
[54] => [email protected]
[55] => [email protected]
[56] => [email protected]
[57] => [email protected]
[58] => [email protected]
[59] => [email protected]
[60] => [email protected]
[61] => [email protected]
[62] => [email protected]
[63] => [email protected]
[64] => [email protected]
)

with the '$userID' being [39] and 'email' being the..well email.

This is where my question comes in.

What I want to achieve is insert a new id in a new column in I have called 'url' in this 'url' column I want to be able to increment by 1 and add the same value to the row with the same email.

So something like this.

UserID |    email        | url  
===============================
  39   |  [email protected] |  1   
  54   | [email protected]  |  1  
  55   | [email protected]  |  1  
  56   | [email protected]  |  1  
  57   | [email protected]    |  2  
  58   | [email protected]    |  2  
  59   | [email protected]    |  2   
  60   | [email protected]    |  2  
  61   | [email protected]     |  3  
  62   | [email protected]     |  3  
  63   | [email protected]     |  3  
  64   | [email protected]     |  3

I'm hoping this makes sense. Thank you for reading. Always appreciate the help.

This smells like a need for a table with id, email (in which id holds the autoincrement column), and a table with userId,url? — Wrikken
– Wrikken, Commented Mar 4, 2013 at 21:03
@Wrikken Hi Wrikken I have simplified the table for this question. Thanks for you input :) — BaconJuice
– BaconJuice, Commented Mar 4, 2013 at 21:10
That does not mean it still doesn't smell like some normalization could be healthy, but hey, you're in a better position to judge whether it's needed than I am ;) — Wrikken
– Wrikken, Commented Mar 4, 2013 at 21:24

Green Black · Accepted Answer · 2013-03-04 22:21:04Z

4

I would go for ON DUPLICATE KEY;

Make sure you have an unique index on email.

if(is_array($myArr)){
 foreach($myArr as $userID=> $email){
  $sql = "INSERT INTO userdata (userID, email) values ('$userID', '$email') 
        ON DUPLICATE KEY UPDATE url=url+1";
  mysql_query($sql) or exit(mysql_error());
 }
}

Edit:

OK, this might be the solution:

$data = array();
$ids = 1;

if(is_array($myArr)){
    foreach($myArr as $userID=> $email){

       $domain = substr(strstr($email,"@"),1);

        if (! isset($data[$domain]))
        {
            $data[$domain] = $ids;
            $ids ++;
        }

        $sql = "INSERT INTO userdata (userID, email,url) 
                VALUES ('$userID', '$email','".$data[$domain]."')";
        mysql_query($sql) or exit(mysql_error());
    }
}

edited Mar 4, 2013 at 22:21

answered Mar 4, 2013 at 21:07

Green Black

5,0571 gold badge19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Othman Over a year ago

This is amazing .. but he wants the + after a new email comes up. doesn't this increment url for each email ? so email 1 - 1234 then email 2 1234

BaconJuice Over a year ago

@John Hi John, your second solution seems to be incrementing the url id with the same email.

Green Black Over a year ago

@BaconJuice I updated my answer. I did not understand the question. You wanted an ID fo each domainname in the emailaddress? My code should work now.

BaconJuice Over a year ago

@John no, its not domain specific. I have it outlined on my question above.

Othman · Accepted Answer · 2013-03-04 21:26:12Z

1

 if(is_array($myArr)) : 

foreach($myArr as $userID=> $email){

    $emails[] = $email;
}

$emails = array_unique($emails);

foreach($emails as $e){
    $counter = 0;
    foreach($myArr as $userID=> $email){

        if($email == $e){

            $sql = "INSERT INTO userdata (userID, email,url) values ('$userID', '$email','$counter')";
            $counter++;
        }
    }

}

endif;

You could create one SQL statement for all the insert by using this.

   if(is_array($myArr)) : 

    foreach($myArr as $userID=> $email){

        $emails[] = $email;
    }

    $emails = array_unique($emails);
    $sql = '';
            $counter = 1;
    foreach($emails as $e){

        foreach($myArr as $userID=> $email){

            if($email == $e){

                $sql .= "INSERT INTO userdata (userID, email,url) values ('$userID', '$email','$counter');";

            }
        }
        $counter++;
    }

    endif;

    mysql_query($sql) or exit(mysql_error());

SQL output

        INSERT INTO userdata (userID, email,url) values ('39', '[email protected]','1');INSERT INTO userdata (userID, email,url) values ('54', '[email protected]','1');INSERT INTO userdata (userID, email,url) values ('55', '[email protected]','1');INSERT INTO userdata (userID, email,url) values ('56', '[email protected]','1');INSERT INTO userdata (userID, email,url) values ('57', '[email protected]','2');INSERT INTO userdata (userID, email,url) values ('58', '[email protected]','2');INSERT INTO userdata (userID, email,url) values ('59', '[email protected]','2');INSERT INTO userdata (userID, email,url) values ('60', '[email protected]','2');INSERT INTO userdata (userID, email,url) values ('61', '[email protected]','3');INSERT INTO userdata (userID, email,url) values ('62', '[email protected]','3');INSERT INTO userdata (userID, email,url) values ('63', '[email protected]','3');INSERT INTO userdata (userID, email,url) values ('64', '[email protected]','3');

edited Mar 4, 2013 at 21:26

answered Mar 4, 2013 at 21:06

Othman

3,0183 gold badges25 silver badges33 bronze badges

4 Comments

Sam Dufel Over a year ago

Well, you can actually only run one query at a time with mysql_query()

Othman Over a year ago

Yes this is my second solution .. and the best.. why hitting the db many times while you can create 1 query.

जलजनक Over a year ago

what he meant; from manual

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Othman Over a year ago

well mysql_query is an old method to access the database why he don't use PDO ? that's the question

Vahe Shadunts · Accepted Answer · 2013-03-04 21:16:17Z

1

You can organize it by 1 query.

if(is_array($myArr)){
 foreach($myArr as $userID=> $email){
  $sql = "INSERT INTO `userdata` (`userID`, `email`, `url`) 
         select '$userID', '$email', count(`email`) +1 from `userdata` where `email` = '$email'";
  mysql_query($sql) or exit(mysql_error());
 }
}

answered Mar 4, 2013 at 21:16

Vahe Shadunts

1,96614 silver badges18 bronze badges

Comments

V_K · Accepted Answer · 2013-03-04 21:10:09Z

0

If you have enough memory - you can create one more array data strcture and store the emails and ID..

For e.g. You can do something like this in your for loop...

$data = array();
$i = 0;

if(is_array($myArr)){
    foreach($myArr as $userID=> $email){
        $url_id = '';

        if (! isset($data[$email]))
        {
            $data[$email] = ++$i;
        }

        $url_id  = $data[$email];

        $sql = "INSERT INTO userdata (userID, email,url) values ('$userID', '$email','$url_id')";
        mysql_query($sql) or exit(mysql_error());
    }
}

I don't think it's most efficient solution.. but it should work..

answered Mar 4, 2013 at 21:10

V_K

2291 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to insert duplicate value depending on what value was entered using mysql and php

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related