mysql: insert/increment unique row depending on prior existence?

[partha]

I have a keywords table with the following columns:

keyword (UNIQUE), related, count

keyword is a single word, each row has a unique value for this column
related is a comma-separated list of other words that are related to that keyword
count is the number of times that keyword has been used in one of my posts

what I want to do is:
when I post a message with an array of associated keywords, the script should look at EACH keyword in the array and EITHER insert a new row in the keywords table if one does not exist for that word, OR increment the count value of the keyword if its row already exists.

I know this would be possible with complicated code and LOTS of queries, but I'm hoping there's a simpler way that lets mysql do most of the work to speed things up.

I've tried this query:

INSERT INTO keywords (keyword, count) VALUES('sarah', count + 1), ('john', count + 1), ('wowza', count + 1), ('fiftynine', count + 1)

but it just comes back saying can't query because a row for "wowza" already exists in the table

[jatar_k]

for each one you need to find out if it exists so

select * from keywords where keyword='mynewkeyword';

if it returns a row then you have the values you need to increment to use in your update or you insert with your count set to 1.

loop through the file for each do your select and insert or update based on return from your select.

[coopster]

There are a couple of other options, too. One is to use INSERT ... ON DUPLICATE KEY UPDATE

[dev.mysql.com...]

and yet another option is to attempt the UPDATE and if mysql_affected_rows() [php.net] is greater than zero you have an update. If not, you can load up a VALUES clause and perform an INSERT for any rows that weren't updated during your UPDATE loop.

[partha]

thanks coopster! that's what I wanted -- something to speed things up by letting mysql take care of the heavy lifting and make my php code simpler