CODEWITHSUNDEEP
mysqlinserton-duplicate-key
Preys
Preys

Reputation: 103

add values with 'on duplicate key'

I have the folowing array called $words

Array
(
[0] => Array
    (
        [token] => dwA
        [pos] => *DII.7,8*
    )

[1] => Array
    (
        [token] => nTr
        [pos] => *DI.5,9*
    )

[2] => Array
    (
        [token] => dwA
        [pos] => *DI.2,8*
    )

[3] => Array
    (
        [token] => nTr
        [pos] => *DI.2,8*
    ))

I want to insert it with the following :

foreach ($words as $value)
    {
    $word = $value['token'];
    $attestation = $value['pos'];
    $insert="INSERT INTO Examples (Word, Attestation) VALUES ('$word', '$attestation')

    mysql_query($insert) OR die(mysql_error());
    }

The table 'Examples' has a key called ID with auto increasement; the field word is 'unique'

what I want is that in the field 'Attestation' the values are added each time the 'word' is the same

so the result should be the following:

Id    word        Attestation
1     dwA         *DII.7,8*, *DI.2,8*
2     nTr         *DI.5,9*, *DI.2,8*

so I tried to add a 'on duplicate key' phrase

 foreach ($words as $value)
    {
    $word = $value['token'];
    $attestation = $value['pos'];
    $insert="INSERT INTO Words2 (Word, Attestation) VALUES ('$word', '$attestation')
          on duplicate key update Attestation = 
 ";
    mysql_query($insert) OR die(mysql_error());
    }

but I can't figure out what I have to add after Attestation = so that the differents attestations are added one after the other, like for example : DI.5,9, DI.2,8 Or is the 'on duplicate key' not the correct way to do this?

Upvotes: 0

Views: 1144

Answers (1)

Chris Henry
Chris Henry

Reputation: 12010

Try this;

INSERT INTO Words2 (Word, Attestation) VALUES ('$word', '$attestation') on duplicate key update Attestation = CONCAT(Attestation, ', ', '$attestation')

Although, it may a bit difficult to break those values apart later on. You may want to consider another table, so you can set up a one-to-many relationship.

Upvotes: 1

Related Questions