Increment column value if duplicate value found

Question

I have a very simple script that imports a CSV of Postcodes and creates a row in a table with it.

I also have a column to store the total number of times that Postcode appears in the CSV file.

Is it possible to increment a column value if a duplicate postcode is found?

So, if there's 2 instances of w1a 1aa it would increment the value in the total column. For example:

id | postcode   | total
0  |  w1a 1aa   | 2
1  |  g74 1bc   | 1
2  |  ml10 6qd  | 1

Table:

id | postcode | total

PHP:

// connect to db
$servername = "localhost";
$username = "";
$password = "";
$dbname = "postcodes";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

foreach($csv as $row) {

    $postcode = $row[0];

    $sql = 'INSERT INTO postcodes VALUES (NULL, "'.$postcode.'", 1)';

    if (mysqli_query($conn, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

}

5

`ON DUPLICATE` ... – Karoly Horvath Nov 21 '16 at 14:44

score 1 · Accepted Answer · answered Nov 21 '16 at 14:53

1

MySQL allows you to phrase something like the following (can't check syntax right now, but you will find it well documented in the internet):

INSERT ... ON DUPLICATE KEY UPDATE...

This means, attempt to insert a row into a table that has a specific column(s) as unique keys and, if a row exists with the same value of key, it executes the update part.

So, if you define the column post code as a unique index, the above construct will do exactly what you are asking for.

answered Nov 21 '16 at 14:53

FDavidov

3,505
6
23
59

Many thanks for your detailed response with this - I shall award a green tick shortly :-) – michaelmcgurk Nov 21 '16 at 14:55
1

You are welcome and thanks. – FDavidov Nov 21 '16 at 14:56

score 0 · Answer 2 · answered Nov 21 '16 at 14:45

0

You'd need to do a select first to check if the postcode you are about to enter already exists, if so get the count and update it against that postcode.

answered Nov 21 '16 at 14:45

Stuart

6,630
2
24
40

1

if you have concurrent requests, this is not safe. – Karoly Horvath Nov 21 '16 at 14:48
1

None of OPs code is safe. This is a suggestion for OP to go and learn! – Stuart Nov 21 '16 at 14:51
@Stuart Thank you. I realise it's not production ready at this stage - thanks for your input :) – michaelmcgurk Nov 21 '16 at 14:53
@Stuart Greatly appreciate your help here, Stuart - I will be very careful when it comes to securing any data input from CSV to my SQL db :) – michaelmcgurk Nov 21 '16 at 14:57
1

All good @michaelmcgurk - The solution by FDavidov is far superior! – Stuart Nov 21 '16 at 14:58

Increment column value if duplicate value found

2 Answers2

Linked