how to add update entity in mysql?

Question

work environment: php + mysql

I want to add an entity from DAO layer to database with the following use case:

Create row in database if it doesn't exist, otherwise, update the record in database.

This needs to be done on a big scale, around 100 million users.

A) In DAO; fetch from database to see if the entity exists. If not, create it in database. If it exists, update the record accordingly.

B) Approach A can be delegated to a procedure in database ? Instead of going back and forth between DAO and DB (which I think is expensive), can we just call a procedure in mysql and let it do the work ?

Also, is there a better way to deal with this situation ?

Scenario:

DB tables

User:
- uid
- name

Post
- pid
- details
- url

UserPosts
- uid
- pid

A user can make many posts. At DAO, I have a user with a list object. I need to add/update the user to database.

what about try an update if that fails insert – T I Dec 28 '11 at 13:38 — T I, Dec 28 '11 at 13:38
same thing: two calls to db per request – brainydexter Dec 28 '11 at 13:42 — brainydexter, Dec 28 '11 at 13:42
what about http://stackoverflow.com/a/2848937/1114171 – T I Dec 28 '11 at 13:48 — T I, Dec 28 '11 at 13:48

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

Here are a few answers I dug up which looks like what you want, basically putting the onus on the db to decide whether to insert or update

for mysql
for postgresql
for oracle

edited May 23 '17 at 11:48

Community

1
1

answered Dec 28 '11 at 13:56

T I

9,785
4
29
51

score 0 · Answer 2 · answered Dec 28 '11 at 13:37

0

//first, check if it exists in database using a SELECT statement   
$sqlCheck = mysql_query("") or die("check");

//determine the row count
$rowCount = mysql_num_rows($sqlCheck);

if ($rowCount == 0) { //if it returns 0 rows, then execute using INSERT statement
     $sqlInsert = mysql_query("") or die(mysql_error());    
}
else { // if there's a record, update the database
     $sqlUpdate = mysql_query("") or die("update"); 
}

hope this helps :)

answered Dec 28 '11 at 13:37

kjG

13
1
3

You described the case A I mentioned. My point here, it takes two calls to database per request which would be a bad idea when we scale the same operation to million of requests. Thoughts ? – brainydexter Dec 28 '11 at 13:43

how to add update entity in mysql?

2 Answers2