Comparing Data that should be UPDATED vs INSERTed

[rkolbe]

Hey all,

So Im stuck for good coding ideas here...

I have a MySQL database, and a php script that does some inserting or updating of data for that DB. Ill be light on the details and nitty gritty so as not to confuse anyone but you will fully understand what I need after reading this 😉

The database has information like ID, SYSNAME, PROGRESS. ID is unique, but so isnt SYSNAME (its the name of a computer...with a scheme).

I have a script that pulls a number (PROGRESS) from flat files on the server, and I want to put this info in the DB...problem is, this script runs every 2 mins (cronjob). I want to UPDATE the computer in the database if the information (PROGRESS) has changed, but there will be some instances where a new flat file (or rather, a new computer added) is added to this setup and the INSERT command for a new computer/row of data will need to be made.

So...this is my theory on how to do this.

• Take the flat file info throw it in an array (no problem, already done).
• Use SELECT to find out the current rows in the database.
• Compare the array from the database (results) to the array from the flat file stuff
• Write an if else statement for an INSERT or UPDATE.

Is there an easier way to do this folks? Any built in functions that can help me? My buddy suggested I do the IF ELSE on the MySQL server using scripting on the DB.

[sneakyimp]

you might want your select query to search only for a duplicate record rather than retrieving all records. mysql is probably faster at doing all the comparisons than any php code you could write.

otherwise, that sounds about right. If you could tease out more info from your buddy we might be able to comment on that as well.

[NogDog]

You could use REPLACE as long as there's a primary key or unique index in the table that it can use to decide if it should insert a new record or update an existing one.