How to update multiple rows in one table (mysql, php, pdo)

Question

the thing is when i left text area blank, i want it to delete that entry

I have a table in which i want to update primary key (RegNo) and (Name)

                              Table students
(Primary Key)                                            (Foreign Key)
 RegNo               Name              Passwd             ProjectID
 f12345              Ali                345                   1
 f12346             Chris               346                   1
 f12347             Ameer               347                   1

I am tried few ways,

$names = [
       ['reg'=> $_POST['s1_id'], 'name'=> $_POST['s1_name']],
       ['reg'=> $_POST['s2_id'], 'name'=> $_POST['s2_name']], 
       ['reg'=> $_POST['s3_id'], 'name'=> $_POST['s3_name']]
       ]; 

 $query="update students SET Name=:Name WHERE RegNo=:reg And 
 ProjectID='$id'";

 foreach ( $names as $name)
 {
   try
  {

    $stmt = $conn->prepare( $query );  
    $stmt->bindParam(':Name', $name['name']);
    $stmt->bindParam(':reg', $name['reg']);

    $result = $stmt->execute();
    $msg = "Record updated";

    //header("location:adminhome.php");
  }

  catch(PDOException $ex)
   {
      $msg = $ex -> getMessage();
   }
  }

Through this way i was able to update Name column only. How i can update both RegNo and Name. I am new to back-end Programming. Don't know how to achieve this.

If I don't see it completely wrong you are overwriting the same dataset (all with PrejectID=$id) over and over again. Or is there an outer loop that we don't see? — Jeff
– Jeff, Commented Jun 21, 2018 at 19:13
@Jeff this code is working fine but its only updating Name. I want to update both RegNo and Name — Mr_question
– Mr_question, Commented Jun 21, 2018 at 19:40

J Quest · Accepted Answer · 2018-06-22 14:23:41Z

1

Something like this? I only added , RegNo=:reg this inside the SET because reg is already bound I think this is all you need to add.

I updated my answer because it will never match when you send an updated RegNo with your data structure. So you should send two RegNo, the old one, and the new one.

For deleting, I define two queries, one for delete and one for the update. Before we update the results I check inside the foreach if the new Students ID ($name['reg_set']) is empty, if it is we run the $query_delete otherwise we will run $query_update

$names = [
       ['reg'=> $_POST['s1_id'], 'reg_set'=> $_POST['s1_id_set'], 'name'=> $_POST['s1_name']],
       ['reg'=> $_POST['s2_id'], 'reg_set'=> $_POST['s2_id_set'], 'name'=> $_POST['s2_name']],
       ['reg'=> $_POST['s3_id'], 'reg_set'=> $_POST['s3_id_set'], 'name'=> $_POST['s3_name']]
       ];

 $query_delete="DELETE FROM students  WHERE RegNo=:reg And ProjectID='$id'";

     $query_update="UPDATE students SET Name=:Name, RegNo=:reg_set WHERE RegNo=:reg And
     ProjectID='$id'";

 foreach ( $names as $name)
 {
   try
  {

            if(empty($name['reg_set'])){
                $stmt = $conn->prepare( $query_delete );
                $stmt->bindParam(':Name', $name['name']);
                $stmt->bindParam(':reg', $name['reg']);

                $result = $stmt->execute();
                $msg = "Record deleted";
            }else{
                $stmt = $conn->prepare( $query_update );
                $stmt->bindParam(':Name', $name['name']);
                $stmt->bindParam(':reg', $name['reg']);
                $stmt->bindParam(':reg_set', $name['reg_set']);


                $result = $stmt->execute();
                $msg = "Record updated";
            }


    //header("location:adminhome.php");
  }

  catch(PDOException $ex)
   {
      $msg = $ex -> getMessage();
   }
  }

edited Jun 22, 2018 at 14:23

answered Jun 21, 2018 at 19:15

J Quest

6033 silver badges17 bronze badges

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

17 Comments

Mr_question Over a year ago

from where i will get " $_POST['s1_id_set']" this value?

Mr_question Over a year ago

@J Quest are you there?

J Quest Over a year ago

Yes, I am, Uehm you have to send it with the form. From where do you receive s1_id ?

Mr_question Over a year ago

<input type="text" id="s1_id" name="s1_id" value="<?php echo $RegNo[0];?>" class="form-control"/>

Mr_question Over a year ago

@J Quest from a form i send this value

|

Collectives™ on Stack Overflow

How to update multiple rows in one table (mysql, php, pdo)

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related