Mysql Insert Or Update Query

[Danny]

Hi All

 

Just after a quick sense-check really to make sure I'm utilising mySQL correctly in terms of load.

 

Here is my script which is working through a file array row by row and then inserts the values from the row into the db, unless the row exists in which case it will update the db entry.

 

(first db field is Primary Key => $myRow[1])

 

$file = file(_PATH_ . 'uploads/' . $filename);

// starting at the 3rd row, work through the file array row by row
for($i = 3; $i < count($file)-1; $i++) {

 // explode each column in the row into an array
 $myRow = explode('|', $file[$i]);

 // sanitize the description
 $brand_name = $dbc->real_escape_string($myRow[2]);

 // insert into the db or if unsuccessful, update existing
 if(!$dbc->query("INSERT INTO tbl_ecc_brand VALUES ('$myRow[1]','$brand_name')")) {
   $dbc->query("UPDATE tbl_ecc_brand SET brand_name ='$brand_name' WHERE brand_id = '$myRow[1]'");
 }
}

 

What are your thoughts, is this how you handle the "insert or update" scenario or is there a better method?

 

Danny

[Danny]

Hmmmmm

 

I'm now actually wondering, if the row exists in the db and the statement defaults to UPDATE, will the query update every row, regardless of whether the $brand_name is different or not, or does it automatically do the check for different values first and pass if no changes?

 

If it doesn't, I would probably need to do a comparison of the $brand_name variable => db field value before I do the update?

[HartleySan]

So you basically have a text file with a bunch of brand names in it, and each brand name is on its own line?

Simply reading the text file in line by line and doing the inserts/updates that way sounds fine to me.

 

However, if you don't want to insert a brand name into the DB if it's already there, you're going to have to run a SELECT statement first to check for the existence of the brand name in the DB. If it's there, do an update, and if it's not do an insert.

 

...Actually, the more I think about it, the more I don't really understand what you're trying to accomplish.

[Danny]

Hi HartleySan

 

Sorry, I should have explained the scenario better.

 

So, to simplify, say I have a text file that has 2 columns, brand_number & brand_name. I read the file into an array and then process it line by line. What I want to do with the data in each line is insert it into a db which also has 2 fields, one for the number and one for the name.

 

Now, as data is added to this file periodically, I will run this process on say, a weekly basis and expect the script to do the following:-

 

1. If the number does not exist, insert the row into the database.

2. If the number does exist but the name has changed (which it occassionally does), update the name.

3. if the number exists and the name is the same, do nothing.

 

To achieve this, I created the script above. I made the brand number a primary key in the db so that basically the script tries to insert the row but if it is unable to (due to primary key duplication) then it does an update.

 

I'm not sure if this is a better method than first trying to do a select statement and then depending on the result of that query, doing an insert or an update.

 

Hope this helps with the understanding.

 

Danny

[HartleySan]

Ah, okay, that makes sense.

Thanks for explaining that.

 

I'm not super familiar with the internals of how the MySQL engine processes requests (but then again, who is?!), so I'm not sure whether it would be quicker to first execute a SELECT statement, checking the current DB status, before possibly executing an INSERT or UPDATE statement, or if it's quicker to do things the way you described. Either way though, unless you're processing a HUGE amount of data, you won't even notice a difference between the two.

 

With that said, I like your idea about attempting an INSERT, and if that fails (because the brand number already exists), attempting an UPDATE. However, I'm not sure if your current code will get you there.

According to the mysqli::query reference page on PHP.net (http://php.net/manual/en/mysqli.query.php), mysqli::query returns TRUE on success and FALSE on a failure. With your current if statement containing the INSERT query, you'll only ever get access to the UPDATE statement if the INSERT query returns TRUE (i.e., is successful).

 

With all that said, looking into your problem more, I learned something new about MySQL: Apparently there is special syntax for forcing an update if an INSERT fails because of a duplicate value. The syntax can be referenced on the following page:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

 

Anyway, to accomplish what you're looking to do, I'd use that MySQL syntax I just referred to. I guarantee it'll be a lot quicker than any logic you can write in PHP.

As a side note, for maximum efficiency, you might also want to have PHP delete the rows in the text file as you process them.

Hope that helps.

[Larry]

What I normally do in these situations is use a REPLACE query. If the primary key value exists, then an UPDATE is performed (on the non-primary key columns). If the primary key value does not exist, then an INSERT is performed.