IF EXISTS UPDATE ELSE INSERT using mysql

Question

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";
  }

did you check ON DUPLICATE KEY in mysql?

Ravi
– Ravi

2018-02-02 04:07:27 +00:00
Commented Feb 2, 2018 at 4:07 — Ravi
– Ravi, Commented Feb 2, 2018 at 4:07

Bits Please · Accepted Answer · 2018-02-02 04:25:53Z

2

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

answered Feb 2, 2018 at 4:25

Bits Please

9071 gold badge7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pawan Developers · Accepted Answer · 2018-02-02 04:07:50Z

0

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, …;

answered Feb 2, 2018 at 4:07

Pawan Developers

3874 silver badges17 bronze badges

Comments

vivek manavadariya · Accepted Answer · 2018-02-02 04:15:00Z

0

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);

answered Feb 2, 2018 at 4:15

vivek manavadariya

2302 silver badges14 bronze badges

Collectives™ on Stack Overflow

IF EXISTS UPDATE ELSE INSERT using mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related