CODEWITHSUNDEEP
phphtmlmysqlhtml-table
aceng juanda
aceng juanda

Reputation: 13

update specific field data on sql database

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??

Upvotes: 0

Views: 936

Answers (3)

KEYUR THUMMAR
KEYUR THUMMAR

Reputation: 16

<?php
    $servername="localhost";
    $username="root";
    $password="";
    $dbname="demon";
    //CREATE CONNECTION
    $conn=new mysqli($servername,$username,$password,$dbname);
    //CHECK CONNECTION
    if ($conn->connect_error) 
    {
        die("connection failed:".$conn->connect_error);
    }
    $sql="UPDATE student set NAME='JohnRambo' where STUDENT_ID=1000";
    $result=$conn->query($sql);
    if ($result===TRUE) 
    {
        echo"NEW RECORD CREATED SUCCESSFULLY";
    }
    else
    {
        echo "ERROR:".$sql."<br>".$conn->error;
    }
    $conn->close();
?>

Upvotes: 0

Secret code enthusiast
Secret code enthusiast

Reputation: 25

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.

Upvotes: 1

Ryan The Ghost
Ryan The Ghost

Reputation: 177

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

Upvotes: 1

Related Questions