CODEWITHSUNDEEP
phpmysqlsql-updatesubmitrecord
parboy
parboy

Reputation: 67

Why is UPDATE creating a new record?

For event listing page. After inserting record into db I capture last ID with

//Store ID[LAST]
    $ID = mysqli_insert_id($db);

Next the page shows event formatted for easy reading. Then user can hold or approve item for publication with 2 buttons: Draft, Publish. I use submits buttons for each:

//Check ID value (use for testing)
    echo "<p>ID = ".$ID."</p>\n";

    echo "<form action='".$_SERVER['PHP_SELF']."' method='GET'>
    <input type='submit' name='submitDraft' value='Draft' />
    <input type='submit' name='submitPublish' value='Publish' />
    </form>";

Now I use conditional to update record according to button chosen. (Note connect.php is include that manages passcodes and connection statement.)

if (isset($_REQUEST["submitDraft"])) {
    include('includes/connect.php');
    $sql="UPDATE sessions SET postit='0' WHERE ID='$ID'";
    if (mysqli_query($db, $sql)) {
            echo "Record updated successfully";
        } else {
            echo "Error updating record: " . mysqli_error($db);
        }

        mysqli_close($db);
    }

    if (isset($_REQUEST["submitPublish"])) {
    include('includes/connect.php');
    $sql="UPDATE sessions SET postit='1' WHERE ID='$ID'";
    if (mysqli_query($db, $sql)) {
            echo "Record updated successfully";
        } else {
            echo "Error updating record: " . mysqli_error($db);
        }

        mysqli_close($db);
    }

My problem is UPDATE statement adds new record to table but should update same record. What is my mistake please.

Upvotes: 0

Views: 611

Answers (2)

parboy
parboy

Reputation: 67

I solve this. Because form resubmit whole page, insert statement is run each time and add new record. This record also get update so appear like UPDATE is add new record. Really INSERT make new record and UPDATE change 'postit' field in same time. To fix I add conditional if $_REQUEST not equal submitDraft or submitPublish then INSERT, else it skip over INSERT. Also I just see same kind of answer from @Jay Patel and @SBA. Thank you to both

Upvotes: 0

Boosuro
Boosuro

Reputation: 497

I think you should hold the last insert id in a session value and use it when the user click Draft or Publish since it appears the insertion of data and events draft or publish happen at different times.

//Store ID[LAST]
$_SESSION['ID'] = mysqli_insert_id($db);

 if(isset($_SESSION['ID'])){
   $ID=$_SESSION['ID'];
   //Check ID value (use for testing)
   echo "<p>ID = ".$ID."</p>\n";
  }


echo "<form action='".$_SERVER['PHP_SELF']."' method='GET'>
<input type='submit' name='submitDraft' value='Draft' />
<input type='submit' name='submitPublish' value='Publish' />
</form>";

if (isset($_REQUEST["submitDraft"])) {
include('includes/connect.php');
 if(isset($_SESSION['ID'])){
   $ID=$_SESSION['ID'];
  }
$sql="UPDATE sessions SET postit='0' WHERE ID='$ID'";
if (mysqli_query($db, $sql)) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . mysqli_error($db);
    }

    mysqli_close($db);
}

if (isset($_REQUEST["submitPublish"])) {
include('includes/connect.php');
if(isset($_SESSION['ID'])){
   $ID=$_SESSION['ID'];
  }
$sql="UPDATE sessions SET postit='1' WHERE ID='$ID'";
if (mysqli_query($db, $sql)) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . mysqli_error($db);
    }

    mysqli_close($db);
}

Upvotes: 1

Related Questions