CODEWITHSUNDEEP
phpmysqlmysqli
Sabil Ahmed
Sabil Ahmed

Reputation: 1

IF EXISTS UPDATE ELSE INSERT using mysql

We are developing an application for our college and in the fees module we need to check whether the student record is already present. If the student record does not exist we need to insert the record else we need to update the record.The code i have used is listed below:

if (isset($_POST['submit'])) {

  for($i=0; $i<count($_POST['mca_1_autogen_id']); ++$i){
   $sql=mysqli_query($conn,"UPDATE `fees_mca_1` set `Fees_paid`='{$_POST['Fp'][$i]}' where Register_number='{{$_POST['Regno'][$i]}'");
}
header("Location:mca1_fees_report.php");
 if(mysqli_affected_rows($conn) == 0){
 for($i=0; $i<count($_POST['mca_1_autogen_id']); ++$i){
   $sql=mysqli_query($conn,"INSERT INTO `fees_mca_1`(`Register_number`,`Student_name`,`Total_fees`,`Fees_paid`) VALUES ('{$_POST['Regno'][$i]}','{$_POST['Sname'][$i]}','{$_POST['Fees'][$i]}','{$_POST['Fp'][$i]}')") or die("Error: ".mysqli_error($dbc));
}}
header("Location:mca1_fees_report.php");
echo "Record Added Sucessfully";
 }

I have also tried this but no avail

if (isset($_POST['submit'])) {

for($i=0; $i<count($_POST['mca_1_autogen_id']); ++$i){
   $sql=mysqli_query($conn,"INSERT INTO `fees_mca_1`(`Register_number`,`Student_name`,`Total_fees`,`Fees_paid`) VALUES ('{$_POST['Regno'][$i]}','{$_POST['Sname'][$i]}','{$_POST['Fees'][$i]}','{$_POST['Fp'][$i]}') ON DUPLICATE KEY UPDATE `Fees_paid` = VALUES({$_POST['Fp'][$i]})");
}
header("Location:mca1_fees_report.php");
echo "Record Added Sucessfully";
  }

Upvotes: 0

Views: 207

Answers (3)

Bits Please
Bits Please

Reputation: 897

You should use this instead:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

It will check if the record is duplicated, then it will update it

Here is the Official Link to Sql Query .

Also please check ON DUPLICATE KEY

Upvotes: 2

vivek manavadariya
vivek manavadariya

Reputation: 230

You should try this

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Upvotes: 0

Pawan Developers
Pawan Developers

Reputation: 377

You should use like this:

INSERT INTO table(column_list)  VALUES(value_list) ON DUPLICATE KEY UPDATE column_1 = new_value_1, column_2 = new_value_2, …;

Upvotes: 0

Related Questions