-2

The problem is when i go to update a row from update.php page it is possible to update a row with same email and roll that has been inserted into the table before. I want to add some conditions that will help me to prevent duplicating the same email and roll.

$exist_query = mysqli_query($link, "SELECT * FROM tbl_student WHERE st_email = '$_POST[email]'");
$row_cnt = mysqli_num_rows($exist_query);
             
if($row_cnt>=1){
   throw new Exception('Email already exists');
}
else {
   $query = "UPDATE tbl_student SET st_name = '$_POST[name]', st_roll = '$_POST[roll]', st_email = '$_POST[email]' WHERE id = $id";
   $query_run = mysqli_query($link,$query);
   $success_message = 'Data has been updaed successfully';
   }

after using these codes the table data is primarily looks like this.

when i tried to update Name from alexa to alex it tells that Email already exists and i cannot update any of these fields. I want to upadte data without duplicating the roll and email address. How can i do that?

More to tell that when I changed the index of st_roll and st_email to UNIQUE it works without duplicating roll and email. After doing UNIQUE i tried this codes

$query = "UPDATE tbl_student SET st_name = '$_POST[name]', st_roll = '$_POST[roll]', st_email = '$_POST[email]' WHERE id = $id";
     $query_run = mysqli_query($link,$query);
      if($query_run){
        $success_message = 'Data has been updaed successfully';
      }
      else {
        throw new Exception('Data already exists');
      }

i am only able to tell that data already exists. But i want to be more specific that do the email is duplicating or the roll number is duplicating? What condition can i apply to be more specific?

N.B: Sorry for my bad English.

Community
  • 1
  • 1
Shahriar Ahmmed
  • 1
  • 1
  • Hi! Could you show some code of what you've tried? Anyway, to update an existing row, you need to use `UPDATE` instead of `INSERT`. Check this page: https://www.w3schools.com/sql/sql_update.asp. Cheers! – Fel Apr 06 '20 at 12:53
  • Does this answer your question? [How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql-i-e-check-if-an-email-exists-in-mysql) – Dharman Apr 06 '20 at 17:25

1 Answers1

1

MAke Email UNIQUE

So no email adress can ever be double entered

With the following command you can make the column meail UNIQUE

ALTER TABLE student
ADD UNIQUE (email);
nbk
  • 45,398
  • 8
  • 30
  • 47