if id exist use on duplicate key, else insert

Question

I have a database with following table test_users:

| id | Username | Password |
|  1 |    pat   |  *****   |
|  2 |   roger  |  *****   |
|  3 |  luke93  |  *****   |

And to insert a new row I use following code, and it works fine:

$sql = $conn->prepare("INSERT INTO `test_users` (`Username`, `Password`) VALUES (?,?)");
$sql->bind_param('ss',$name, $email);

But now i am trying to make a "update profile"-page and I wanted to use ON DUPLICATE KEY. That means I need to check if idexists and if so update the row. Neither Username or Password is Unique, but id is. I have a $_SESSION["id"] which is available if the user is logged in. Can I use that in some way? So how do I write a SQL-sentence that finds out if id exist, and if so, overwrite it with ON DUPLICATE KEY (or a better way)?

Try if exists() function. Use the below link [Solution](http://stackoverflow.com/questions/12639407/sql-if-exists-update-else-insert-syntax-error) — Harsha W, Apr 18 '17 at 04:47
Assign unique key of your unique field and try below query http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — Suresh Suthar, Apr 18 '17 at 04:49
check - https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html — Sougata Bose, Apr 18 '17 at 04:51

score 0 · Accepted Answer · answered Apr 18 '17 at 04:46

0

first write selct query and count num rows if its 0 then insert query fire else update query

answered Apr 18 '17 at 04:46

Nishit Manjarawala

451
4
20

I considered that, but hoped there was a solution for it with ON DUPLICATE KEY. But that is not possible? – William82 Apr 18 '17 at 04:52
when u inserting if filed autoincrement primary then how you get id? – Nishit Manjarawala Apr 18 '17 at 04:56
You get your Id when you are logged in. It is saved in a session. If you are not logged in, you will not have a session with your id – William82 Apr 18 '17 at 05:03
INSERT INTO test_user(id, username, password) VALUES($_SESSION['id'], "test", "test") ON DUPLICATE KEY UPDATE username="test", password="test" – Nishit Manjarawala Apr 18 '17 at 05:11

score 0 · Answer 2 · answered Apr 18 '17 at 04:50

0

UPDATE works the same as an insert. You just need to pass the WHERE condition. You can do this with the following code, Try it

$id = $_SESSION['id'];
$sql = "UPDATE test_users SET Username=?, Password=? WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param($Username, $Password, $id);
$stmt->execute();

answered Apr 18 '17 at 04:50

Manjeet Barnala

2,975
1
10
20

Yes, I know, that was the best solution I found, but I was hoping some people here knew a better way around this. – William82 Apr 18 '17 at 04:54
I don't think so that there is other solution than 'ON DUPLICATE KEY UPDATE' – Manjeet Barnala Apr 18 '17 at 05:10
@William82 what issues you are getting and how you are using it ?? show me your code so that I can help you.. Thanks – Manjeet Barnala Apr 18 '17 at 07:51

score 0 · Answer 3 · edited May 23 '17 at 12:02

0

Assign unique key of your unique field and try below query Insert into a MySQL table or update if exists

INSERT INTO test_user(id, username, password) VALUES(1, "test", "test") ON DUPLICATE KEY UPDATE    

username="test", password="test"

edited May 23 '17 at 12:02

Community

1
1

answered Apr 18 '17 at 04:51

Suresh Suthar

794
8
15

but with that one I always will update row['id'] = 1, right? – William82 Apr 18 '17 at 04:54
@William82 you can made id dynamic, this is just example – Manjeet Barnala Apr 18 '17 at 05:03
so how can I make it dynamic and auto increment? – William82 Apr 18 '17 at 05:15
@William82 select auto increment option in database, then after don't insert id on insert time, – Suresh Suthar Apr 18 '17 at 05:37

score 0 · Answer 4 · answered Apr 18 '17 at 06:01

0

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

credits to Donnie

answered Apr 18 '17 at 06:01

xcution

91
5

how does this work. It says value 1 in Id. Will it not update only where Id is 1? And if it is dynamic, what variable should I then use? – William82 Apr 18 '17 at 06:12

if id exist use on duplicate key, else insert

4 Answers4