How to insert new row and update old row's 1 column field?

#1

I have a table where I need to insert data but when inserting I need to update its old row. Suppose the data is Id, Name, Designation, CardData, ExpiryDate, Status: Now when I insert the data I need to update the status field of the old row data.

Would Insert into table Values() on Duplicate key Update help me?

#2

create trigger after insert ...

1 Like
#3

I don’t see how that would help, unless you are inserting duplicate data, in which case, you shouldn’t be doing an insert to begin with.

The trigger would be how I would do it, you just have to remember the trigger is there, because it can screw with you when you are testing later on.

1 Like
#4

Okay Thanks I will do that. @astonecipher @chorn

#5

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

13.2.5.2 INSERT … ON DUPLICATE KEY UPDATE Syntax

1 Like
#6

Hi all,

 I am still stuck with this problem where I am unable to insert the data as well. Please guide me.
#7

and what do you have now?

#8

All I need to do is : onclick firstly I need to populate data if it exists and there only I need to create a form to enter those data. Now if the user wishes to update the data which exists then he needs to fill the form, and now on submission of this form I need to delete the previous data and update with the new one.

Any suggestions please.??

#9

carddata

On click of existing card details I need to show existing data as shown in the image. As you can see that there are input type text boxes where user can put new card details.
Now all I want is when I enter the data it must update/delete previous record.

#10

then make a form that contains the ID + all the fields to update and on submit do update table(cols, ...) values(?, ...) where id = ?

#11
$sql = "SELECT * FROM bta_card_details WHERE tgi_id = '".$username."'";
    $result = mysqli_query($conn, $sql);  
    if($result){
    echo '<form id="myForm" action = "bta_generate_request.php" method = "POST" onsubmit = insertDetails();><h1>Your Card Details are:</h1><table id="msgs"><caption><b>Important Messages</b></caption><thead><tr><th>Card Number</th><th class="wider">Card Expiry Date</th><th>Bank Name</th></tr></thead><tbody>';
    while($row = mysqli_fetch_assoc($result)) {
      echo '<tr><td width="120">' .$row["bta_card_num"]. '</td><td width="120">' .$row["bta_card_expiry_dt"]. '</td><td contenteditable="true">' .$row["bta_card_bankName"]. '</td></tr>';
    }
    echo '<div id="input1" style="margin-bottom:4px;" class="clonedInput">
        Card Number: <input type="text" name="name1" id="name1" /> 
        Expiry Date: <input type="date" name="date" id="expirydate" />
        Bank Name: <input type="text" name="name2" id="bankName" />
        <input type="hidden" name="bta_submit" id="bta_submit" value="Submit" style="width:60px;"/>
    </div> </tbody></table>   
</form>'; 
  }
#12
function insertDetails(){
  if((isset($_POST['bta_submit'])) || (isset($_POST['exists'])) || (isset($_POST['cardDetails']))) {
    $cardNum = $_POST['name1'];
    $expiryDate = $_POST['expirydate'];
    $bankName = $_POST['bankName'];

    $sql = mysqli_query($link, "INSERT INTO bta_card_details (`bta_card_num`,`bta_card_expiry_dt`,`bta_card_bankName`,`tgi_id`,`status`) VALUES('".$cardNum."', '".$expiryDate."', '".$bankName."', '".$username."', '1')");
    echo "INSERT INTO bta_card_details (`bta_card_num`,`bta_card_expiry_dt`,`bta_card_bankName`,`tgi_id`,`status`) VALUES('".$cardNum."', '".$expiryDate."', '".$bankName."', '".$username."', '1')";
    if($sql){
      echo "Inserted";
    }
    else{         
      echo "Failed";
    }
  }
}
#13

nice that it works for you?

#14

It is not working. I have written the code in this form but I am not quite sure as to where am i going wrong.

#15

And what does that mean? What did you try to find the error by yourself? What did you try to solve your problem? You should reflect that other people don’t sit in front of your screen, don’t have insights of your brain and can’t reproduce anything as you are only posting random snippets that depend other codes.

#16
I will post the files you can check it. Below is the div where I am clicking on showdata function to fetch card details from database.

<div id="Cardform"><input type="checkbox" class="group" name="card" id ="cardDetails" onclick = "showData();">Existing Currency Card</div>

JS File
function showData()
{
    var $checks = $('input:checkbox.group');
     $.ajax({
        type:'POST',
        url:'ajaxData.php',
        success: function(data){
            if(!$checks.filter(':checked').length == 0) {
                console.log(data);
                $('#exists').html(data);                   
                $('#exists').show();
            }
            else{                 
                $('#exists').hide();
            }
        },
        error: function(){                   
                alert(" No Card details");
        }
        });
}

My ajaxdata.php file is as shown above which I will re-post it and you can see it:

$sql = "SELECT * FROM bta_card_details WHERE tgi_id = '".$username."'";
    $result = mysqli_query($conn, $sql);  
    if($result){
    echo '<form id="myForm" action = "bta_generate_request.php" method = "POST" onsubmit = insertDetails();><h1>Your Card Details are:</h1><table id="msgs"><caption><b>Important Messages</b></caption><thead><tr><th>Card Number</th><th class="wider">Card Expiry Date</th><th>Bank Name</th></tr></thead><tbody>';
    while($row = mysqli_fetch_assoc($result)) {
      echo '<tr><td width="120">' .$row["bta_card_num"]. '</td><td width="120">' .$row["bta_card_expiry_dt"]. '</td><td contenteditable="true">' .$row["bta_card_bankName"]. '</td></tr>';
    }
    echo '<div id="input1" style="margin-bottom:4px;" class="clonedInput">
        Card Number: <input type="text" name="name1" id="name1" /> 
        Expiry Date: <input type="date" name="expirydate" id="expirydate" />
        Bank Name: <input type="text" name="bankName" id="bankName" />
        <input name="submit" type="button" value="Submit">
    </div> </tbody></table>   
</form>'; 
  }
  else{
         echo "Failed";
  }
#17

This is the file where I am inserting i.e., bta_generate_request.php file

#18

And what now? There’s big bunch of dependencies i don’t have on any of my machines. You still don’t tell what you problem is and just ran over the questions i asked.

#19

I have been trying to insert the data that I enter into the form. I get no errors as well but its still no inserting into database. I tried running the code in different page, there the data is inserting. You can see the img below img
carddetails

#20

use Prepared Statements.