Avoiding duplications of data in uploads, and amending existing data

silasslack

Hi All,

I have buit a system whereby people upload a list of outstanding ordered items with dates, etc, as a tab delimited file exported from excel. So far so good, and they can edit individual parts of the file in the site.

What I need them to be able to do however is to edit the file in excel with differing information in it, and re-upload it. At the moment, this will just string the new lines onto the bottom of the table, what i would like to happen is for the upload to amend existing fields instead of creating new ones. The identifier to check if the line already exists would have to be a combination of 3 fields: customer_reference, vendor_reference and item_number.

Does anybody have any pointers about how this might be achieved? All help appreciated

Silas

johanafm

Are all those fields present when the user submits data? And are those three fields the primary key?

If so, INSERT ... ON DUPLICATE KEY ...

If you only lack the primary key you can also create a unique index on those three fields.

silasslack

johanafm;10929464 wrote:
Are all those fields present when the user submits data? And are those three fields the primary key?

If so, INSERT ... ON DUPLICATE KEY ...

If you only lack the primary key you can also create a unique index on those three fields.

The Primary Key exists in the table itself for 'internal' purposes, but it doesn't exist in the data that a user will upload. I want to identify which data they are uploading is duplicate data and may contain amendments, and which data is new data and should simply be added in at the bottom.

The way I see it is that the closest i can get to a unique identifier is the combination of order reference and item number (as this combination cannot possibly be repeated, and exists in both places).

The problem I have is how to use this information to both amend existing lines and add new lines.

if I use "INSERT ... ON DUPLICATE KEY ..." can i specify several fields, the combination of which must be unique, to be the identifier for the data that will be overwritten?

Many Thanks for your help

Silas

djjjozsi

you can find in phpmyadmin: add key on X fields. Then specify which fields you need, and the type of the key: UNIQUE

If this is an updater system, once you exported the data from your table, save the primary key in a field. Then your program then make a choice:
-if the primary key does exist, update the database with the corrected values.
-If the primary key is empty, then insert as new data.

johanafm

Yes, the result of insert ... on duplicate key is handled by the database. If you have a multicolumn unique constraint, then this is what's checked.

silasslack

Many Thanks! that's solved my problem

Silas