How can i find which table is updated or affected by website action for a large my sql database

Question

How can i find which table is updated or affected by website action for a large database. Is there any software available or any other way? How can we check which table record is updated/deleted/inserted, If we do not have any knowledge about flow of database table. Like if we complete check out process and do not know about how many tables are updated for record then how can we check for my sql database which table is updated. May be a log is created but i do not have any knowledge, please help me! Thank you

Answer 1

You could try enabling mysql query log. You can read this post and this other post which explain how to do it.

Answer 2

id | time | date | items | name | email | last_edited <-add this

Then on each view / change to the query or item you can update the date.

$sql = "UPDATE item SET last_edited=now() WHERE id='$viewed' LIMIT 1";
if (mysqli_query($data_base_connection, $sql)){echo 'success';exit();}
else {echo 'Connection Error'; exit();}

There is a way to fix that, you can always look at the logs, but if you want to echo it out nicely you can do this.

$sql = "SELECT * FROM item ORDER BY last_edit DESC LIMIT 10";
$query = mysqli_query($data_base_connection, $sql);
if(mysqli_num_rows($query) < 1){echo 'No Results.';} 
else {
    while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
        $name = $row["name"];
        $last_edit = $row["last_edit"];
        echo 'NAME: '.$name.' LAST EDITED: '.$last_edit.'<br>';
    }
}