CODEWITHSUNDEEP
mysql
shire
shire

Reputation: 1

Is it possible to declare to mysql queries?

I'm trying to create a code for a single button where it will perform either of two actions where it will add to the database if the user currently don't have the record while it will update the user's record if the user has records already. I've done it like this:

if() {
   mysql_query("INSERT INTO table...");
}
else {
   mysql_query("UPDATE table SET...");
}

Is it possible?

Upvotes: 0

Views: 141

Answers (6)

Lucky13
Lucky13

Reputation: 11473

Yes it is possible , it will work

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

Yes, what you've written will work. If you have a way to know if there already exists a row or not without making an additional query just for this bit of code, then do exactly as you wrote.

If, however, you planned to first SELECT from the table to see if a row exists, then conditionally INSERT or UPDATE, you will perform more queries than necessary.

It would be better to either:

  1. Have a PRIMARY KEY or other constraint on the table prevent duplicate INSERTs. Then issue an INSERT ... ON DUPLICATE KEY UPDATE query. This will attempt to INSERT the row, and if it is a duplicate, automatically perform the specified UPDATE to that row instead.

  2. Issue the UPDATE query and check mysql_affected_rows to see if it updated an existing row. If not, then issue the INSERT query to create the new row.

Which one is more appropriate depends on your application.

Upvotes: 2

Gary Barnett
Gary Barnett

Reputation: 204

Yes, you could try the insert then if it fails try the update.

But you could use the MYSQL sql "REPLACE" keyword, which will insert a new record if it doesn't exist or delete the existing record and insert your new one if it does.

You could also use the INSERT ... ON DUPLICATE KEY UPDATE syntax (explained here - Link to MYSQL ref which seems to be the closest fit to your requirement.

Upvotes: 1

Haim Evgi
Haim Evgi

Reputation: 125526

you can use INSERT ... ON DUPLICATE KEY UPDATE Syntax like:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

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

Upvotes: 2

enthusiastic
enthusiastic

Reputation: 742

yes it is possible

first write a query for check that record is already exist or not.

Upvotes: 0

Simone
Simone

Reputation: 11797

If you have properly set unique keys, you should use REPLACE so you could remove the if.

REPLACE INTO table VALUE (...);

Pay attention that this is a MySQL extension, thus not portable to other DBs.

Upvotes: 1

Related Questions