how to check for duplicate entry before update in php + mysql?

Question

check for duplicate "username" field

case :

if user wants to update record other than username field (like firstname or whatever) and i used simple select query to check for duplicate like

"SELECT * from user where username = $username"

and check count than it will return count 1 because user name already exist (in same record)

case :

if user change username (it is unique now) and check for duplicate using same query than it returns 0 and it works fine

so if user doesn't change username than query above breaks

so i changed query like:

"SELECT * 
   FROM user 
  WHERE `username` = $username 
    AND user_ID != $uid" (here user_ID is primary key)

so it will not check current record so, is it correct or not ?

EDIT :
my project is based on mvc ,modal have global function like

insert($tablename,$condition)

update($tablename,$condition)

Etc and update funciton works like
create sql query (dynamically based on $condition)
Execute query with $result = $query->execute();
and

 if ($result) {
   $msg = 'true';  
 } else {
     $msg = 'false';
 }

  $data = array(
    'result' => $msg,
      'query' => $sql,
     'eventType' => 'Update'
   );
  return $data;

so how can i catch duplicate entry exception (i mean on which colum it occured (when multiple unique key exist))

You have an uneven number of quotation marks – Strawberry Apr 15 '21 at 06:01 — Strawberry, Apr 15 '21 at 06:01
And '==' isn't a thing in MySQL – Strawberry Apr 15 '21 at 06:02 — Strawberry, Apr 15 '21 at 06:02
it's doen't matter, i just want to know about logic – Razin Shaikh Apr 15 '21 at 06:07 — Razin Shaikh, Apr 15 '21 at 06:07

score 1 · Accepted Answer · answered Apr 15 '21 at 06:02

1

The best way to do SQL is to have the structure enforce the contrants:

If you want a unique user name, declare a unique key on it. ALTER TABLE user ADD UNIQUE KEY username_uniq(username)

Don't try to detect it up front, that will always be subject to race conditions.

Catch the duplicate key error exception when and UPDATE/ INSERT is done which violates the constraints.

answered Apr 15 '21 at 06:02

danblack

12,130
2
22
41

just wanted to know if i don't have database access than my logic is correct? – Razin Shaikh Apr 15 '21 at 06:09
1

no. no. its not. – danblack Apr 15 '21 at 06:11

how to check for duplicate entry before update in php + mysql?

1 Answers1