How to insert new data if user_id does not exist in mysql table

Question

I have two columns into mysql table map 1. First column have name json, second have name user_id Now, I need to insert data to new row if user_id does not exist into mysql table, but if excist I need just to update json column where user_id = user_id ...

I try:

     try {        
                $STH = $db->prepare("INSERT INTO map (user_id, json)
VALUES (:2,:1)
on duplicate key update json=values(json)");


                $STH->bindParam(':1', $_POST['mapData']);
                $STH->bindParam(':2', $user_id);

Some ideas? I use php pdo.

How to solve this problem?

Thanks!

not user, user_id... so if exist I need just to update json column of that row where user_id = user_id... I store user_id at every table - so I know which is data... — johny mile, Feb 08 '15 at 22:14
But if the user doesnt exist you dont have a user id. I think this is your issue. Wouldnt you then want to insert a new user and grab that id? — djv, Feb 08 '15 at 22:15
No, I use ajax, so when user want to insert to map mysql table , then i chech if users is loggen,if yes then I store his id and his data mapData from frontend... — johny mile, Feb 08 '15 at 22:19

score 0 · Answer 1 · answered Feb 08 '15 at 22:19

Just have a look at MySQLs REPLACE command. Cite from MySQL manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

That could be the solution to your problem. Otherwise, make a select, if the id exists, make an update, otherwise an insert.

score 0 · Accepted Answer · edited May 23 '17 at 12:28

You miss : in on duplicate key update json=values(:json)");, so try like this:

     try {        
                $STH = $db->prepare("INSERT INTO map (user_id, json)
VALUES (:user_id,:json)
on duplicate key update json=values(:json)");
                $STH->bindParam(':json', $_POST['mapData']);
                $STH->bindParam(':user_id', $user_id);

Besides, your question is related to: Here

How to insert new data if user_id does not exist in mysql table

2 Answers2