CODEWITHSUNDEEP
phpmysql
selva s
selva s

Reputation: 27

how to check the database is update/modify/changed?

This is my database

enter image description here

This is my php code which is used to fetch the data from the table

    while($row = mysqli_fetch_array($result))
{
    $user_id =  $row['id']; 
    $sql = "SELECT id, ScheduleDate, StartTime,Endtime, Hours,Employeeid 
    FROM empdet WHERE Employeeid ='".$user_id."' ";
    $result = $con->query($sql);     
    if ($result->num_rows > 0) 
    {
    // output data of each row
    while($row = $result->fetch_assoc()) 
        {
        $id=$row["id"]. 
        $date=$row["ScheduleDate"]; 
        $start=$row["StartTime"]; 
        $end=$row["Endtime"];
        $hour=$row["Hours"];
        $Employeeid=$row["Employeeid"];
        list($year,$month,$day) = split("-",$date);
        $data[] = array("year"=>$year,
                        "month"=>$month,
                        "day"=>$day,
                        "StartTime"=>$start,
                        "Endtime"=>$end,
                        "Hours"=>$hour );   
        }
            $response = $data;
    }
    else
        {
        $data=1;
        $response = $data;
        }

My question is how to find whether the database is modified/changed/updated?

In the users table, if I change the start time from 2:00pm to 3:00 pm of "2018-12-01" it should notify me in echo as the "2018-12-01" has been modified.

Can anyone suggest how to do this with PHP?

Upvotes: 0

Views: 4405

Answers (3)

Mazhar Hussain
Mazhar Hussain

Reputation: 125

you create a column with timestamp using this query

ALTER TABLE `table_name` ADD `time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `column_name`;

timestamp create auto time when data enter so you can get lastime of data and checkout max timestamp

Upvotes: 0

Sufyan
Sufyan

Reputation: 21

int mysql_affected_rows ([ resource $link_identifier = NULL ] ) Returns the number of affected rows on success, and -1 if the last query failed.

see http://php.net/manual/en/function.mysql-affected-rows.php

Upvotes: 2

Professor Abronsius
Professor Abronsius

Reputation: 33823

You could make use of the timestamp column type in the database which, when set to use auto-update, would give an indication that the record has been updated.

alter table `empdet`
    add column `timestamp` timestamp null default current_timestamp on update current_timestamp after `Employeeid`;

Then you could alter your query to determine if the record is updated

select `id`, `scheduledate`, `starttime`, `endtime`, `hours`, `employeeid`,
case 
    when `timestamp` is not null then
        true
    else
        false
end as 'updated'
from `empdet` where `employeeid` ='".$user_id."';

However, looking at the original code there appears to be nested loops both trying to process the query result variable $result with the inner query re-assigning this variable. As the outer loop is providing an ID for the inner loop it would make more sense perhaps to do a join - but hard to be specific without seeing the previous code and knowing what the original query was.

Upvotes: 0

Related Questions