update specific field data on sql database-CodePudding

so i have a table of data in web ui

table

as soon as I click the button. all of the field data in "Status Email" changed. not just selected field that i meant. this is the sintaks sql

if($mail->Send())
{
    $query = "UPDATE nearly_inactive SET EmailSent = 'Sudah Kirim Email' WHERE  EmailSent = 'Belum Kirim Email'";
    $update = $con->prepare($query);
    $update->execute();     
}

how can i get the "update" only the data that I click on the button??

CodePudding user response：

Get specific field

In order to get the specific field from a MYSQL database

Select column FROM databse WHERE x = y

Example:

SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'

The issue

It's best to get a unique identifier, which no other user has used. For example a 10 digit user id code. Check that this code doesn't exist, for it to be unique.

UPDATE:

Easily use the UNIQUE SQL tag to resolve this issue.

CREATE TABLE X (
    ID INT UNIQUE 
)

Example:

SELECT id, firstname, lastname FROM MyGuests WHERE id=ryan9273__2

Update a specific field

Now that we have fixed the issue we can easily

UPDATE x SET y=z WHERE id=b

Lets fix your code:

UPDATE nearly_inactive SET EmailSent = 'Sudah Kirim Email' WHERE EmailSent = 'Belum Kirim Email'

Lets make it more dynamic

UPDATE nearly_inactive SET :email = :emailaddr WHERE EmailSent = :id

final code:

    $query = $con->prepare("UPDATE nearly_inactive SET :email = :emailaddr WHERE EmailSent = :id");
    $query->bindParam(':email', $email, PDO::PARAM_STR);
    $query->bindParam(':emailaddr', $emailaddr, PDO::PARAM_STR);
    $query->bindParam(':id', $id, PDO::PARAM_STR);
    $update->execute();

Security Matters

You are using PDO, so use bindParam aswell. Secret code enthusiast answer isn't as secure as the current code i provided!

Practice Makes Perfect

Please don't copy my code right away. learn from it and code it again ! Make it better. Also check the official PHP documentation for more info on these topics

Stay safe !

Regards,

Ryan

CodePudding user response：

you need to determine which record need to be changed based on their unique ID. usually it's the primary key of the table. so, If your primary key is enroller_id, then pass the value of enroller_id, and put it inside your sql.

if($mail->Send())
{
    //prepare your query
    $statement = $this->mysqli->prepare("UPDATE nearly_inactive SET EmailSent = 'Sudah Kirim Email' WHERE enroller_id = ?");
    
    //check for statement preparation
    if ($statement === false) {
        trigger_error($this->mysqli->error, E_USER_ERROR);
        return;
    }

    //bind the value
    $statement->bindParam("i", $id);

    //get id for the query
    $id = your_field_enroller_id;

    //execute the statement
    $statement->execute();  
}

where enroller_id is your table primary key, and $id is the value of that field primary key.