How can I create a field-based history for tracking changes in a database using PHP?

To create a field-based history for tracking changes in a database using PHP, you can create a separate history table that stores the old and new values of the fields being tracked along with a timestamp and user identifier for each change. Whenever a field is updated in the main table, you can insert a record into the history table capturing the old and new values. 

// Assuming you have a main table named 'users' and a history table named 'user_history'

// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=your_database", "username", "password");

// Get the old and new values of the field being updated
$old_value = $pdo->query("SELECT field_name FROM users WHERE id = :id")->fetchColumn();
$new_value = $_POST['new_value'];

// Insert a record into the history table
$stmt = $pdo->prepare("INSERT INTO user_history (user_id, field_name, old_value, new_value, change_date) VALUES (:user_id, :field_name, :old_value, :new_value, NOW())");
$stmt->execute(array(':user_id' => $id, ':field_name' => 'field_name', ':old_value' => $old_value, ':new_value' => $new_value));

// Update the field in the main table
$stmt = $pdo->prepare("UPDATE users SET field_name = :new_value WHERE id = :id");
$stmt->execute(array(':new_value' => $new_value, ':id' => $id));

Keywords

PHP database field-based history tracking changes version control

Related Questions