Check if record is already in MySQL

[jpr]

Hello,
I have to create a asset database system for our assets at work. I have created the web app, and created the import feature which takes a tab-delimited import file from our vendor, parses, and inserts the data in to our database. The problem is that the report that we import could have entries that we already have recorded in our database.

If I parse a record from the file, read the serial number for that asset, what is the best way to make sure that that same serial number is not already recorded in my database before doing an INSERT for that record? The only way I knew how to do this before was to load the current database in to an array, and then check the import file records against the array by using the in_array(). This is obviously a horrible way to to this, loading the entire database in to an array first.

Database:

---

| Description | Serial |

| Computer | cnu567 |

| Laptop | nnv345 |

I would appreciate any suggestions. Please let me know if I can clarify what I am trying to do any better. Thank you!

Using: PHP 5.2.3, MySQL 5.0.45

-Jason

[NogDog]

First thing is to ensure that the serial number column is uniquely indexed in the database (likely as the primary key?). Then, the question is what do you want to happen if there is a duplication of serial numbers? If you want to overwrite the data for the current one with the data from the new file, then do a REPLACE INTO query instead of an INSERT INTO query. If you want to keep the original data and return some sort of error, then you can do an INSERT query, and if it fails then check to see if mysql_errno() == 1062 (duplicate key error) and report the error accordingly.